Forum Discussion
Resolver IDATEADD minus 1 year not working
Hello,
I am trying to get the same calculated measure for the prior year.
Here is my first measure, caculating the YTD value of sales:
Measure = CALCULATE (
SUM ( POS[POS Net Revenue]),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Calendar Date] <= MAX ( 'Calendar'[Calendar Date])
&& YEAR ('Calendar'[Calendar Date] ) = YEAR ( MAX ('Calendar'[Calendar Date] ) )
)
)
This returns for me the rolling total from the selected year on my slicer (2021). It also works properly when I select a month.
NOW, all I want to do is the same measure, but for the prior year. When I do this for just the sum of the values using this formula:
Measure 3 = CALCULATE(sum(POS[POS Net Revenue]), DATEADD('Calendar'[Calendar Date], -1, YEAR))
I get these results - which are correct.
I did the same thing for this measure
Measure 2 =
VAR _CurrentYTD = CALCULATE (
SUM ( POS[POS Net Revenue]),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Calendar Date] <= MAX ( 'Calendar'[Calendar Date])
&& YEAR ('Calendar'[Calendar Date] ) = YEAR ( MAX ('Calendar'[Calendar Date]))
))
Return
CALCULATE(_CurrentYTD, DATEADD('Calendar'[Calendar Date], -1, YEAR))
And this happens:
It just gives me the same results, and does not go back a year.
What am I doing wrong here?!?!
Thanks,
Joe~
When I opened up the calendar table I thought "there must be some conflict between the DAX time intelligence and the fiscal month number". I haven't looked in detail as to what's happening (though I've seen this sort of thing before) but I wrote this:
Prior YTDChilli = VAR _tYear = MAX('Calendar'[Fiscal Year]) VAR _tMonth = MAX('Calendar'[Month Number]) RETURN CALCULATE(SUM(POS[Net USD]), FILTER(ALL('Calendar'), 'Calendar'[Fiscal Year] = _tYear - 1), 'Calendar'[Month Number] <= _tMonth)I removed all time intelligence functions and tried to return what you need.
Please test at your side.
9 Replies
- HotChilli
Community Champion
I've seen people try this sort of thing before. I think there's a belief that the DAX in the variable acts like a piece of dynamic sql and will recalculate once it's placed inside another DAX statement. However all that happens is that the variable evaluates a specific value and then placing the (now evaluated value) in another DAX statement doesn't alter it, it's already got a value.
Incidentally, I think the first measure ("measure") is just the same as
SUM ( POS[POS Net Revenue])That's what it looks like anyway.
jwin2424I have changed the measure to look like this
Current YTD = TOTALYTD(SUM(POS[POS Net Revenue]),'Calendar'[Calendar Date])and this
Prior YTD = TOTALYTD(SUM(POS[POS Net Revenue]),SAMEPERIODLASTYEAR('Calendar'[Calendar Date]))
And I get this:
For some reason, it works perfectly for 2021 vs 2020, but no other year works. It starts in month 2 vs month 1 prior year and continues to skip down the rows. The YoY for the entire year remains correct, but not the months.
Any help is appreciated!
- HotChilli
If you put together a sample pbix and link it here, I'll have a look
- jwin2424
Thank you. Can you walk me through how to do that? I dont see any option in the reply to upload a file.
- HotChilli
You'll have to put it on a 3rd party site(box, onedrive etc) and post the link here.
--
If you don't want to post it on the forum, send me the link through private message.
- HotChilli
Yes, I got it.
- HotChilli
When I opened up the calendar table I thought "there must be some conflict between the DAX time intelligence and the fiscal month number". I haven't looked in detail as to what's happening (though I've seen this sort of thing before) but I wrote this:
Prior YTDChilli = VAR _tYear = MAX('Calendar'[Fiscal Year]) VAR _tMonth = MAX('Calendar'[Month Number]) RETURN CALCULATE(SUM(POS[Net USD]), FILTER(ALL('Calendar'), 'Calendar'[Fiscal Year] = _tYear - 1), 'Calendar'[Month Number] <= _tMonth)I removed all time intelligence functions and tried to return what you need.
Please test at your side.
- jwin2424
This is exactly right! I could not figure out how to do it without using time intellegnce. It seems like at some point I had to some version of time intellegence.
Thank you!
On a side note, I would love to know WHY my time intellegence would not work for prior year but would work for current year. I am guessing it has something to do with DAX trying to calculate based on calendar date 1 through 31 even though I am filtering on fiscal month and year. I also don't know if I need to turn off auto calendar in the settings. I did change my table to the date table, but I know DAX still uses the default calendar for time intellegence.
Lastly, I don't know how the MAX function operates. I was trying those filters as well, but it kept returning the current date. I just didn't use the MAX function. I tried just filtering by fiscal year - 1. Knowing might help me greatly in any other time intellegence functions I try with our weird company fiscal calendar.
THANK YOU!