Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PavelR
Solution Specialist
Solution Specialist

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

@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!
Blog
Twitter
LinkedIn

View solution in original post

55 REPLIES 55
PavelR
Solution Specialist
Solution Specialist

@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

 

@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!
Blog
Twitter
LinkedIn
PavelR
Solution Specialist
Solution Specialist

@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

 

@PavelR

I see...that could be bothersome having unwanted months showing up.

I would suggest that you have at least complete months in your Date table covering your Data date range, so you could redefine Date as:

Date = 
CALENDAR (
    EOMONTH ( MIN ( Data[Date] ), -1 ) + 1,
    EOMONTH ( MAX (Data[Date] ), 0 )
)

Given the way SAMEPERIODLASTYEAR, DATEADD and other time intelligence functions work, you will get funny results without complete months.

 

I updated my PBIX file above, and when 2017 is selected, a Month slicer will show only month 1.

 

On your question of why your intended behaviour isn't the default behaviour: Firstly, I agree that your intended behaviour is probably common to many businesses and I have seen it before. However, the time intelligence functions are intended to work with a Date table, without considering the exact dates that may be present in your fact table(s). It is possible that January has passed, but you had transactions on only 1-3 January. There is no way the DAX engine could know that January had concluded from the fact table alone, unless 'zero' rows were created for dates without transactions, or some other flag showed the last date.

 

Perhaps another approach would be to include full months in the Date table, but also include a binary column that indicates whether a date has passed or not, and that column could be referenced in your measures (rather than my approach which assumed the last date in the entire fact table is the latest date).

 

Cheers,

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
PavelR
Solution Specialist
Solution Specialist

Thanks again @OwenAuger. Your comment with completed actual month in date table was really helpful for me.

 

Regards.

Pavel

PavelR
Solution Specialist
Solution Specialist

Hi @OwenAuger,

could you please help me with construction of formula to calculate Sales MTD LM (Last month)?

 

So I have standard measure for Sales MTD

Sales MTD = TOTALMTD(SUM(Data[Amount]);'Date'[Date])

And I would like to have measure to show me Sales MTD for last month - so when now is 17.1.2017, I have sales from 1.1.2017 to 17.1.2017. And then I would like to have Sales MTD LM from 1.12.2016 to 17.12.2016.

 

Is it possible, when the year in filter is set to 2017?

 

Thanks.

Pavel

Hi @PavelR

 

I would suggest a similar pattern to the YTD LY measure.

You can use DATEADD instead of SAMEPERIODLASTYEAR in this case.

INTERSECT(...) returns the selected date range limited by the latest date in the Data table, then DATEADD shifts it one month earlier.

 

Sales MTD Last Month (limited by last date in Data table) =
VAR DataMaxDate =
    CALCULATE ( MAX ( Data[Date] ); ALL ( Data ) )
RETURN
    CALCULATE (
        [Sales MTD];
        DATEADD (
            INTERSECT (
                VALUES ( 'Date'[Date] );
                DATESBETWEEN ( 'Date'[Date]; BLANK (); DataMaxDate )
            );
            -1;
            MONTH
        )
    )

Cheers,

Owen 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
PavelR
Solution Specialist
Solution Specialist

Works perfect! Smiley Happy Thanks a lot @OwenAuger.

 

Regards.

Pavel

Anonymous
Not applicable

It is working well, but when I do filters like country or city, it still shows the same value and filters are not applying to this. Where I need to change if I want to apply filter.

 

Thanks,

v-huizhn-msft
Employee
Employee

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

Anonymous
Not applicable

do the table calendar have to be related with the data table?

Anonymous
Not applicable

Yes the date table needs to be related to data table - you can see that in the sample file provided in solution.

 

Anonymous
Not applicable

thank you so much

@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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.