sameperiodlastyear not working as I'd expect

[Anonymous]

I am attempting to write two measures:

1. A rolling year count

2. The prior rolling year count

To calculate the rolling year count I am using CurrentMonthOffset (-1 to -12) filters:

#CMI Den RollingYr OFFSET = 
  CALCULATE(
         [#MSDRGsWithWeight - CMS (CMI Den)],
             FILTER(
                 ALL(CalendarTable), 
                   CalendarTable[CurrentMonthOffset] <= -1 &&  -- Between previous month
                   CalendarTable[CurrentMonthOffset] >=-12))   -- & 12 months ago

When I attempt to calculate the prior rolling year count using the sameperiodlastyear function I do not return expected results. :

#CMI Den PriorRollingYr SamePeriodLastYear = 
       CALCULATE(
           [#CMI Den RollingYr OFFSET],
            SAMEPERIODLASTYEAR(CalendarTable[CALENDER_DT]))

I am able to calculate the prior rolling year count by again using CurrentMonthOffset filters (-13 to -24) but I would like to avoid this in order to have the measures reference eachother to make for easier maintenance, etc...

#CMI Den PriorRollingYr OFFSET = 
  CALCULATE(
      [#MSDRGsWithWeight - CMS (CMI Den)],
       FILTER(
          ALL(CalendarTable),
             CalendarTable[CurrentMonthOffset] <= -13 &&
             CalendarTable[CurrentMonthOffset] >=-24))

Would anyone be able to explain to what I am doing wrong/not understanding about the use of sameperiodlastyear function in the context of the second measure above?

Thank you very much!

Tyler

[Whitewater100]

Hi:

Can you consider using this for your first measure and then DATEADD for the second.

#CMI Den RollingYr 

 =

var lstdate = LASTDATE('Date'[Date])

var frstdate = NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Date'[Date])))

return

IF(ISBLANK([

[#MSDRGsWithWeight - CMS (CMI Den)]

), BLANK(),

CALCULATE(

[#MSDRGsWithWeight - CMS (CMI Den)]

,

DATESBETWEEN('Date'[Date],frstdate, lstdate)))

Rolling 12M LY = CALCULATE([#CMI Den RollingYr], DATEADD(Dates[Date], -1,YEAR))

I think hard coding the month index might be influencing your result for LY.

[Whitewater100]

Hi:

The main thing might be missing is a Date Table, marked as such. If it's continuous, and marked as a date table then you can take advantage of time intel built-in calculations, like SAMEPERIODLASTYEAR.

I beleive to make your second one work you can set up a variable for year e.g,

var LY = MAX(

'Calendar Table'[Year])-1 

and work it into your filter statement. e.g.

But if you get your date table marked it will be easier and better for you. Connect Date table to the date field in 

#MSDRGsWithWeight

An example how the function looks like for an sales measure looks like this: with [Total Sales] as measure we are branching out from. Then you can do many amazing calulations with far less code.

12 M RT =

var lstdate = LASTDATE('Date'[Date])

var frstdate = NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Date'[Date])))

return

IF(ISBLANK([Total Sales]), BLANK(),

CALCULATE([Total Sales],

DATESBETWEEN('Date'[Date],frstdate, lstdate)))

LY 12 M RT = CALCULATE([12 M RT],

SAMEPERIODLASTYEAR(Dates[Date]))

[Anonymous]

Hi,

Thanks for this response. I tried specifically marking a date table but that did not resolve the issue.

It seems to me like the first measure,  #CMI Den RollingYr OFFSET, is not passing any usable date context to the second measure, #CMI Den PriorRollingYr SamePeriodLastYear, for which to apply SamePeriodLastYear function to. I'm not quite understanding why. I can satisfy the report needs by again hardcoding CurrentMonthOffset logic (in the third measure above, #CMI Den PriorRollingYr OFFSET) but would like to dynamically link the measures instead. 

Here is a table of the output of the three measures. As you can see, it looks like the second measure, #CMI Den PriorRollingYr SamePeriodLastYear, is not incorporating any date filters and is just returning the same output as the first measure. 

[Whitewater100 · Accepted solution]

Hi:

I was thinking, a calculation like 12 month running total won't make sense to do SAMEPERIODLASTYEAR on. The 12 month RT will adjust no matter where you have your date filter. If you select 2020 or 2021 it will give you the 12M RT in a dynamic way. Sorry about that, however the first measure I gave you is perfect for 12M RT. Hope this helps..

[Anonymous]

I think I am following you part way here. Can you just elaborate on why it doesn't make sense to use SAMEPERIODLASTYEAR for a 12 month RT measure?

Does SAMEPERIODLASTYEAR require filter context (date filter) to work and because the 12 month RT measure calculates independent of filter context it will not work? Is that sort of the reason? I am wondering if I am confused here between filter context and filters built into a measure. 

[Whitewater100]

Hi Thaling - the reason I suggest that is your measure is always going back over the past 12 months to get a full 12 months of sales. If you filter on Year-Month 2022-03 then it goes back to 2021 April 1 - EO March this year. The RT measure is designed to smooth out your sales over the years and using this one measure since say 2018 to now will show you the moving trend, in 12 month buckets. 

To me it would be confusing to ask for a measre to capture sales between 13 and 24 months ago in a sliding manner in the same way as the first measure is designed to show the trend  over time/years. Basically it's not needed. But if you want it or someone else it can be done but again I feel like it's not very intuitive and could confuse people. Just my thoughts. However having 3 or 6 or 12 month RT is very much accepted. If you only have a year or two of data maybe 3 or 6 month trend is better. I hope this helps..

[Anonymous]

I see what you are saying. That makes sense to me.

The other piece I was hung up on, and what I understand better by playing more with the functions, is that SAMEPERIODLASTYEAR requires filter context in order to work.

Filter context meaning: Filter coordinates coming from the pivot.

If I use logic indepedent of filter context (like the below code) this will not work if passed to the SAMEPERIODLASTYEAR function because there is no filter context after the ALL() function is used.

FILTER(
                 ALL(CalendarTable), 
                   CalendarTable[CurrentMonthOffset] <= -1 &&  -- Between previous month
                   CalendarTable[CurrentMonthOffset] >=-12))   -- & 12 months ago