cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ATL_Adam Frequent Visitor
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.

 

Screen Shot 2016-09-23 at 12.39.05 PM.png

 

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.

 

@v-ljerr-msft @kcantor @Greg_Deckler @greggyb

 

 

Screen Shot 2016-09-23 at 12.43.13 PM.png

 

 

5 REPLIES 5
Super User
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))

greggyb New Contributor
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] )
    )
)
ATL_Adam Frequent Visitor
Frequent Visitor

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

@kcantor @greggyb

 

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
greggyb New Contributor
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.

 

Capture.JPG

Snap Frequent Visitor
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.