topic Re: DATEADD minus 1 year not working in DAX Commands and Tips

DATEADD minus 1 year not working

jwin2424 — Wed, 30 Mar 2022 18:24:16 GMT

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~

Re: DATEADD minus 1 year not working

HotChilli — Wed, 30 Mar 2022 21:04:50 GMT

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.

Re: DATEADD minus 1 year not working

jwin2424 — Thu, 31 Mar 2022 20:28:20 GMT

I 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!

Re: DATEADD minus 1 year not working

HotChilli — Fri, 01 Apr 2022 11:09:25 GMT

If you put together a sample pbix and link it here, I'll have a look

Re: DATEADD minus 1 year not working

jwin2424 — Fri, 01 Apr 2022 16:34:32 GMT

Thank you. Can you walk me through how to do that? I dont see any option in the reply to upload a file.

Re: DATEADD minus 1 year not working

HotChilli — Fri, 01 Apr 2022 17:22:51 GMT

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.

Re: DATEADD minus 1 year not working

jwin2424 — Fri, 01 Apr 2022 19:08:02 GMT

https://1drv.ms/u/s!Ar0KGFeE8jBeamtWyVKAtJHjTII?e=KfRK59

Let me know if this link works.

I have a few formulas in there. I can calculate prior year sales. This aligns with my calendar filters. I can also do YTD and receive a correct sum. When I do prior YTD, it skips the first month. The year is correct, but it is accurate for 10 out of 12 months. My other prior YTD calculation is where I am trying to filter it based on the selected slicer year MINUS 1, and it just returns the current YTD. I am not sure what I am doing wrong here, and it is driving me crazy.

Re: DATEADD minus 1 year not working

HotChilli — Fri, 01 Apr 2022 19:47:50 GMT

Yes, I got it.

Re: DATEADD minus 1 year not working

HotChilli — Sat, 02 Apr 2022 00:24:18 GMT

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.

Re: DATEADD minus 1 year not working

jwin2424 — Sat, 02 Apr 2022 20:47:44 GMT

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!