Forum Discussion

Charinite's avatar
Charinite
Frequent Visitor
6 years ago
Solved

Month Over Month Change

I'm trying to bring the previous month sales totals in the Matrix and use it to Calculate growth and GAP as given above(yellow cells):

 

Opening_LM = CALCULATE(SUM(SUBS_Base_Data[Current Mth USD Amt Post Renewal (Original FX Rate)]),FILTER(SUBS_Base_Data,SUBS_Base_Data[Month]="Previous"),DATEADD(SUBS_Base_Data[PeriodDate],-1,MONTH))

 

EndingACV_LM = CALCULATE(SUM(SUBS_Base_Data[Current Mth USD Amt Post Renewal (Original FX Rate)]),FILTER(SUBS_Base_Data,SUBS_Base_Data[Month]="Current"),DATEADD(SUBS_Base_Data[PeriodDate],-1,MONTH))

 

to calculate the total for the LM cells but it is returning blanks in the matrix.

Please help me in understanding what I'm doing wrong here.

Please let me know if I need to add more information.

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    Charinite for any time intelligence, you must add calendar dimension in your model, there are many posts on how to do this and then use that for previous month values.  This is the best practice and way to do this kind of calculation.

     

     

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Charinite for any time intelligence, you must add calendar dimension in your model, there are many posts on how to do this and then use that for previous month values.  This is the best practice and way to do this kind of calculation.

     

     

    • Charinite's avatar
      Charinite
      Frequent Visitor
      6 years ago

      @ parry2k Thanks for the help. I had a calendar dimension table in place. I was not using it correctly. I just had to replace the date header for the array and it worked like a charm.

      Thank you.