cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Solution Supplier
Solution Supplier

YTD last year DAX

Hi,

 

I have question about YTD Last year DAX formula. Even though I have read many blogs and topics here in community, I still don't know how to solve this issue.

Suppose I have data like below.

 

IDDateAmount
101.01.201610
202.01.201615
303.01.201611
410.01.20168
520.02.20167
601.01.201710
702.01.201712
803.01.20179

 

I would like to figure up YTD value of amount for actual data (year 2017) and then YTD value for corresponding period previous year.

 

For YTD I use formula:

 

YTD = CALCULATE(SUM(Data[Amount]);DATESYTD(Date[Date]))

 

For YTD LY I use formula:

 

YTD LY = CALCULATE([YTD];SAMEPERIODLASTYEAR(Date[Date]))

 

Then when I use these formulas in PBI, I have got incorrect value for YTD LY. I expect value 36 (dates: 1.1. - 3.1. 2016), but in total I have got 44 (it also calculates Amount 8 in 10.1.2016) - so it gives me period of january 2016, but I want only the corresponding days from 2017.

But YTD LY value in pic below on row for 3.1.2017 is OK.

 

YTD_LY.PNG

 

Does anybody know the solution?

 

Thanks.

Regards.

Pavel

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: YTD last year DAX

@PavelR

 

It should work fine in Power BI.

You didn't have any dates in your Data table beyond 3 Jan 2017 did you?

 

Here is a sample PBIX file with your data posted above where the measure is working:

PBIX file

 

I made a small change to the YTD measure so that it is not displayed after the max date in Data table, but the YTD LY measure is the same as I posted.

 

Have a play with that - there must be some difference in your model if it is not working.

 

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

49 REPLIES 49
Highlighted
Microsoft
Microsoft

Re: YTD last year DAX

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.

1.PNG 2.PNG

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

Best Regards,
Angelia

Highlighted
Solution Supplier
Solution Supplier

Re: YTD last year DAX

@v-huizhn-msft thanks, I really appreciate your answer.

 

I have already used calendar function to create date table in example - named "Date". Facts are in table named "Data".

So my formulas for YTD and YTD LY are the same as yours - only with different namings of tables.

 

I don't get it why YTD LY shows me data for whole January of previous year - I just want the dates till 3.1., so from 1.1. - 3.1.2016. I also don't want to use filter of day. I just want it to be in default the valu 36 only with year filter. I use it to show KPI on dashboard where I want to present actual YTD value in comparison with YTD LY value - but for the corresponding period, so 1.1-3.1.2016 for YTD LY and 1.1.-3.1.2017 for YTD.

 

Is that possible?

 

Thanks indeed.

Regards.

Pavel

 

 

Highlighted
Community Champion
Community Champion

Re: 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 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Solution Supplier
Solution Supplier

Re: YTD last year DAX

Hi @OwenAuger, thanks for your response,

 

when I implement your measure to PBI I get the same value of 44, as in previous YTD LY measure. Am I wrong with something? Is there some additional prerequisite in PBI or something?

 

Thanks and regards

Pavel

 

Highlighted
Community Champion
Community Champion

Re: YTD last year DAX

@PavelR

 

It should work fine in Power BI.

You didn't have any dates in your Data table beyond 3 Jan 2017 did you?

 

Here is a sample PBIX file with your data posted above where the measure is working:

PBIX file

 

I made a small change to the YTD measure so that it is not displayed after the max date in Data table, but the YTD LY measure is the same as I posted.

 

Have a play with that - there must be some difference in your model if it is not working.

 

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Highlighted
Solution Supplier
Solution Supplier

Re: YTD last year DAX

Awesome @OwenAuger, thanks a lot!

 

I really appreciate your effort. I will try it implement to my PBI file.

 

Could you also explain to me please, why the function SAMEPERIODLASTYEAR doesn't do that comparison as default behavior? I mean the comparison of the corresponding previous period? Why it compute YTD LY as the whole month (value 44)?

 

I think that my requirement ("business case") is quite standard, but the solution is not so easy Smiley Happy

 

Regards.

Pavel

 

Highlighted
Solution Supplier
Solution Supplier

Re: YTD last year DAX

@OwenAuger I've just found the prerequisity I had asked before. It is connected with the way how the date dimension is created. You use CALENDARAUTO function, but I use formula with CALENDAR function.

 

Date = CALENDAR(MIN(Data[Date]);MAX(Data[Date]))

 

When there is implemented CALENDARAUTO function, everything works fine. When CALENDAR function implemented it doesn't work - again I get 44 value.

 

Why is that so please? I don't want to use CALENDARAUTO function because of the fact, that you then see dates that have not occured.

 

Regards.

Pavel

 

Highlighted
Community Champion
Community Champion

Re: YTD last year DAX

@PavelR

Ah - I think I see the problem then.

 

In general, a calendar table should cover complete years to ensure correct behaviour of time intelligence functions.

 

I used the CALENDARAUTO function as I was being lazy and I knew it expands the date range to cover complete years.

Since you used the CALENDAR function covering the date range in Data[Date], your calendar ended on 3/1/2017.

 

To fix this, using the CALENDAR function, you could redefine your Date table as:

Date = 
CALENDAR (
    DATE ( YEAR ( MIN ( Data[Date] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( Data[Date] ) ), 12, 31 )
)

The reason that the a calendar table ending on 3/1/2017 didn't behave as intended is that DAX viewed January 2017 as a 3 day month, and the SAMEPERIODLASTYEAR function would have translated 1-3 Jan 2017 => 1-31 Jan 2016.

 

Cheers,

Owen 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Solution Supplier
Solution Supplier

Re: YTD last year DAX

@OwenAuger As I said, I don't want to have all dates of 2017, I only want the dates to actual day. Because of this I have used MIN and MAX dates from Data table.

 

When I use your solution, I see all month of 2017 even though I have only 3 (today 4 days) of year 2017 - and that is what I don't want.

 

toOwen.PNG

 

Thanks.

Pavel

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors