Forum Discussion
Solution SpecialistYTD last year DAX
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
Solution SpecialistHi 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
Super User
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
- PavelR
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 :smileyhappy:
Regards.
Pavel
- PavelR
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
- OwenAuger
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 :)
- ToddChitt
@OwenAuger: I tried your DAX formula and it works, to an extent. It works for the current year. I'm looking to display a table that shows the following three columns: [Year], [YTD], [YTD Last Year].
To test your formula I built a simple Excel sheet with two columns: [Sale Date] and [Sales] and populted it with every date between 1/1/2015 and today (8/18/2017) and $1.00 of Sales on each day (hey, makes it easy to test, right?). This table is joined to a fully populated Date table with dates ranging back and forward several years.
After adjusting your formula and building my table, I see this:
2017 is showing correct LYTD value of 231 (one over this year because of Feb. 29 2016) but Year 2016 should show 230. Instead it show 365.
Seems LYTD should be a VERY simple calculation in DAX. (MDX had the function ParallelPeriod that made this SUPER EASY!) I've spent hours pouring over blogs, forums and technet and still can't seem to get it.
Frustrated.
Thanks in advance.
- OwenAuger
The requirement from the original post was to createa a "YTD Last Year" measure that restricts the dates for "last year" just in the special case where the current date filter context goes past the last date for which data exists.
The reason for even wanting such a measure is that the built-in behaviour of time intelligence functions is to shift the current Date table filter context in some way. So if 2017 is selected on the Date table, the filter context is 1 Jan 2017 to 31 Dec 2017, and "last year" is 1 Jan 2016 to 31 Dec 2016, regardless of whether any data exists relating to particular dates within those years.
It sounds like you're wanting something slightly different...
Is it correct that you want you YTD Last Year measure to always look at the number of days for which data is present in the "latest" year (2017 in your example), and restrict the date context accordingly when shifting back dates not only from 2017 but also 2016 or any earlier year?
Is this because in your model, "YTD" is defined relative to the actual date when the model is refreshed (i.e. today in the real world), rather than the current date filter context?
Would you still want the YTD measure to behave as they currently are, or also restrict all years based on the latest year?
It can all be done, just a matter of defining the desired behaviour of the measures.
Regards,
Owen
- ToddChitt
Thanks for the reply.
>Is it correct that you want you YTD Last Year measure to always look at the number of days for which data is present in the "latest" year (2017 in your example), and restrict the date context accordingly when shifting back dates not only from 2017 but also 2016 or any earlier year?<
Not exactly. YTD should look at the current date, and apply that respective end date to the previous years. If today is the 22nd of August, there is no fact data beyond today, and for prior years I would expect to see data from 1/1/2016 up through 8/22/2016, and likewise 1/1/2015 up through 8/22/2015, and on down the years. Come September 2, 3, and 4, (Labor Day holiday weekend in the US) there will be no fact data generated, but the filter context should be current date, not the last date for which we have data.
I'm usually pretty good with DAX but this one seems to have me stumped. I do have YTD measure defined as follows:
YTD = TOTALYTD ( [Base Measure], 'Dates'[Date] )
Thanks in advance.
-Todd
rajibmahmudHelper III
OwenAuger, is it possible to calculate YTD based on fiscal year instead of calendar year (i.e. Starting at Apr 1, instead of Jan 1)?
- Ashish_Mathur
Just wanted to say thanks OwenAuger for your solution . Saved me lot of anxiety and time :)
I used the following formula to find YTD and YTD LY
YTD = CALCULATE(SUM('data'[konto]),
FILTER('year',[Date]>=DATE(YEAR(TODAY()),1,1) && [Date]
<=(DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1)))YTD LY = CALCULATE(SUM('data'[konto]),
FILTER('year',[Date]>=DATE(YEAR(TODAY())-1,1,1) && [Date]
<=(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)-1)))when I select year 2018 It only showes me YTD, how can I fix this to look both YTD and YTD LY when year 2018 is selected?
- Ashish_Mathur
Hi,
What exactly are you trying to do? Describe your question in detail and share some data.
haribPost Patron
Anonymous
It's working fine, but i want to show whatever date values are there in the last year. In below example i want to display 10.01.2016 as well
ID Date Amount
1 01.01.2016 10
2 02.01.2016 15
3 03.01.2016 11
4 10.01.2016 8
5 01.01.2017 10
6 02.01.2017 12
7 03.01.2017 9Is it possible to show please let me know
Hi Owen
I had the same probleme and it caused me quite a headache.
Your solution helped me a lot. Many thanks for that.
All I need to do now is to understand the mechanics properly.
Thanks again.
