cancel
Showing results for
Did you mean:
Highlighted
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.

 ID Date Amount 1 01.01.2016 10 2 02.01.2016 15 3 03.01.2016 11 4 10.01.2016 8 5 20.02.2016 7 6 01.01.2017 10 7 02.01.2017 12 8 03.01.2017 9

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.

Does anybody know the solution?

Thanks.

Regards.

Pavel

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I

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

49 REPLIES 49
Highlighted
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.

Best Regards,
Angelia

Highlighted
Solution Supplier

Re: YTD last year DAX

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
Super User I

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

Highlighted
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
Super User I

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

Highlighted
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

Regards.

Pavel

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
Super User I

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

Highlighted
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.

Thanks.

Pavel

Announcements

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors
Top Kudoed Authors