YTD last year DAX

PavelR

It sounds like you want your YTD LY measure to detect the last date that appears in your Data table, and only translate dates up to that date back to last year within SAMEPERIODLASTYEAR.

You can use a pattern like this (note the distinction between Data[Date] which I assume is a column of Data, and Date[Date]):

YTD LY (limited by last date in Data table) =
VAR DataMaxDate =
    CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) )
RETURN
    CALCULATE (
        [YTD],
        SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( Date[Date] ),
                DATESBETWEEN ( Date[Date], BLANK (), DataMaxDate )
            )
        )
    )

DataMaxDate is the global maximum date in your Data table. You can define it differently if you want to apply different logic.

Cheers,

Owen :)

Hi PavelR,

SAMEPERIODLASTYEAR Function Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, it will change with actual date is you use it combine DATESYTD function. And SAMEPERIODLASTYEAR requires consecutive dates in the filter context. In your given sample data, the data column is not consecutive. You'd better create calendar date table using the formula:

Calendar = CALENDAR(MIN(Table6[Date]),MAX(Table6[Date]))

Create the two measure using the formulas below.

YTD = CALCULATE(SUM(Table6[Amount]),DATESYTD('Calendar'[Date]))

YTD LY = CALCULATE(Table6[YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))

For your issue, it because the slicer just includethe year rather day level, it will return the the same period about month in last year. The 44 equals the total of Jan. If you create a filter including day level, it will get the expected result like the following screenshot.

 

If you have any other issue, please feel free to ask.

Best Regards,
Angelia

First of, thanks for all the help on the issue OwenAuger however, I have a problem which I couldn't resolve at all since I am kind of beginner with DAX.

My dataset has unique Sales Year-Month-Day values from 01.01.2016 to 05.20.2017 in date format and was related with a Calendar Date column which ends at 05.20.2017 as well since it's the last data collection date.

First I was struggling to compare MoM for 2016 and 2017 sales. For example, May of 2017 contains only 20 days of data and I wanted to compare the same date range for May of 2016; however, it was fetching all the 2016 data till the end of the year. I solved that issue with your explanation.

For this year sales I used: 

This Year Sales = CALCULATE(SUM([SalesRevenue]);DATESYTD('Calendar'[Date].[Date]))

And for last year sales:

Last Year Sales = 
VAR DataMaxDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ); ALL ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        [This Year Sales];
			SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( 'Calendar'[Date].[Date] );
                DATESBETWEEN ( 'Calendar'[Date].[Date]; BLANK (); DataMaxDate )
            )
        )
    )

Since my intention was comparing apples to apples, this was the result I was looking for. I finally was able compare the first 20 days of May 2016 and first 20 days of May 2017. 

However, now I am not able to visually filter any of the months. I mean, when I click Last Year Sales, it's automatically accumulating This Year Sales as well. Please see below:

My intention is:

1. To be able to individually filter every year's sales like I was able to before.

2. If I cannot filter individually because of the formulas I used, I am kind of looking for a new way to compare last year's and this year's sales. Like I mentioned, last data date must be taken into account for last year's datas.

3. I created a different Calendar.[Date] and columns related with my sales date data. (Format is in date like 01.01.2016)

Thanks for all the help, rgrds,

Hello Friends,

I am trying to calculate YTD from previous years, 2022, 2021, 2020.... I dont want to show the full year amount but compare against current YTD, for example Today 4/APR/2023 against 4/APR/2022/2021/2020... Etc, i am connected to a DataSet hence I cannot modify the model but create DAX measures locallly. I will appreciate any input, help from your side. 

Thank you. 

Luis

Step 1. find YTD = TOTALYTD([Total Sales],CALENDAR[Period],"3/31") - for FY from April-March.

Step 2.

VAR MAXDATE =MAX(CALENDAR'[Period])

RETURN

CALCULATE([TOTAL YTD],SAMEPERIODLASTYEAR(DATESBETWEEN(CALENDAR[Period],BLANK(),MAXDATE)))