Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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:
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
@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
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 🙂
@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
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 🙂
Thanks again @OwenAuger. Your comment with completed actual month in date table was really helpful for me.
Regards.
Pavel
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 🙂
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,
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
do the table calendar have to be related with the data table?
Yes the date table needs to be related to data table - you can see that in the sample file provided in solution.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
86 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |