Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rolling 12 Month Without current Month

Hello, 

 

I need an advice. I'm trying to calculate the Rolling 12M and what I need when it comes to the months of rolling 12  is as below 

If I'm looking in Jan 2022 then my 12 month previous should be Jan 2021 -Dec 2021 not Feb 2021-Jan 2021. Ideally in the the 12M the current month should be excluded. 

 I used the below DAX but ofcourse here the start date is Max(Date[day]). I tried replacing that with dateadd function to pick up a table to set up the start date month as previous month. But nothing was working 

 

Sales LY_R12 =
CALCULATE([Sales],DATESINPERIOD('Date'[Day],MAX('Date'[Day]),-12,MONTH))
 
Any Advice would be really helpful.
 
KR,
Sandeep 
Rolling 12 Month Without current Month
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , try like

     

    Sales LY_R12 =
    CALCULATE([Sales],DATESINPERIOD('Date'[Day],eomonth(MAX('Date'[Day]),-1) ,-12,MONTH))

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , try like

     

    Sales LY_R12 =
    CALCULATE([Sales],DATESINPERIOD('Date'[Day],eomonth(MAX('Date'[Day]),-1) ,-12,MONTH))
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks a lot amitchandak . I used another logic eventhough I was getting the answer my total was not reading the last month total and your dax was giving the right totals as well.