cancel
Showing results for
Did you mean:
Frequent Visitor

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

1 ACCEPTED SOLUTION
Super User III

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

2 REPLIES 2
Super User III

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.