cancel
Showing results for
Did you mean:
Frequent Visitor

## Cumulative Rolling Total (This month, last month, 2 months ago) Line Chart.

So I've read and read and wasted more hours than I care to admit trying to solve this problem. I am trying to show current MTD units of sales MTD for this month, which I have solved.

 Cumulative Sale Total MTD = CALCULATE(COUNTA('Sales Occuring'[SaleId]), FILTER(ALL('Sales Occuring'), 'Sales Occuring'[Sale_Date__c] <= MAX('Sales Occuring'[Sale_Date__c]) && MONTH('Sales Occuring'[Sale_Date__c]) = MONTH([Today])))

Which gives me what I want to see.

However what I want to show is MTD this month w/ MTD Last Month and 2 Months ago. For some reason I am only able to do this by aggregating the data into the Date Table which doesn't allow me to slice it properly and I know isn't the ideal solution.

Below is the goal I am trying to reach.

Hopefully some people who have been helpful on related posts can help me solve this. I've got a feeling I am missing something very simple and have been too stubburn to figure it out on my own. Thanks in advance to anyone who can help.

5 REPLIES 5
Super User

## Re: Cumulative Rolling Total (This month, last month, 2 months ago) Line Chart.

Have you tried simply using dateadd?

Last Month = CALCULATE([Cumulative Sale Total MTD, DATEADD(Date[DateKey], -1, Month))

2 Month Prior = CALCULATE([Cumulative Sale Total MTD, DATEADD(Date[DateKey], -2, Month))

New Contributor

## Re: Cumulative Rolling Total (This month, last month, 2 months ago) Line Chart.

A FILTER()-based alternative to @kcantor's suggestion. This depends on a date table that has a monotonically incrementing MonthIndex field and a DayOfMonth field.

```// DAX
// These leverage a MonthIndex which doesn't wrap from 12 to 1
// moving from December of year 1 to January of year 2, rather
// continues incrementing, such that January of year 2 is 13, of year
// 3 is 25, and so on.
// DayofMonth is simply the result of calling the DAY() function - an
// integer between 1 and 31 (inclusive).
MTD -1 Month =
CALCULATE(
[Sales]
,FILTER(
ALL( DimDate )
,DimDate[MonthIndex] = MAX( DimDate[MonthIndex] ) - 1
&& DimDate[DayOfMonth] <= MAX( DimDate[DayOfMonth] )
)
)

MTD -2 Month =
CALCULATE(
[Sales]
,FILTER(
ALL( DimDate )
,DimDate[MonthIndex] = MAX( DimDate[MonthIndex] ) - 2
&& DimDate[DayOfMonth] <= MAX( DimDate[DayOfMonth] )
)
)```
Frequent Visitor

## Re: Cumulative Rolling Total (This month, last month, 2 months ago) Line Chart.

Thanks for the feedback, but both of these suggestions would only show MTD for this month with the two previous months. What I am trying to achieve is MTD for this month, overlayed with the day by day trend for the entire month for the two previous months.

Highlighted
New Contributor

## Re: Cumulative Rolling Total (This month, last month, 2 months ago) Line Chart.

This is your chart. That's the measure I posted.

I used the exact pattern I posted. My data stops on today's date.

Frequent Visitor

## Re: Cumulative Rolling Total (This month, last month, 2 months ago) Line Chart.

Thanks greggyb This worked for me, do you know how I can make the current month not flatline for dates yet to come? Or is that asking too much.