DATEADD minus 1 year not working

[jwin2424]

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~

[HotChilli · Accepted solution]

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!  

[HotChilli]

Yes, I got it.

[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.

[jwin2424]

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. 

[HotChilli]

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.

[jwin2424]

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!