Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

sameperiodlastyear not working as I'd expect

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's avatar
    Whitewater100
    4 years ago

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

7 Replies

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Whitewater100
        Icon for Solution Sage rankSolution Sage
        4 years ago

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

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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.