Say today is 5/22, I want to dynamically calculate the YTD sale of the last five years. 2024: sales from 1/1/24-5/22/24 2023: sales from 1/1/23-5/22/23 2022: sales from 1/1/22-5/22/22 and so on.
I have tried multiple DAX formulas but I could only get the sales of entire the month of May from previous year but I only want to sum them to the current date. Thanks in advance!
maybe you can try to create a flag column in date table.
flag =
IF (
YEAR ( TODAY () ) - YEAR ( 'Date'[Date] ) < 5
&& MONTH ( 'Date'[Date] ) <= MONTH ( TODAY () )
&& DAY ( 'Date'[Date] ) <= DAY ( TODAY () ),
"y"
)
then all the date that you need will be marked as 'y'