Reply
Frequent Visitor
Posts: 2
Registered: ‎09-23-2016

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.

 

@JerryLi @kcantor @smoupre @greggyb

 

 

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

 

 

New Contributor
Posts: 596
Registered: ‎08-27-2015

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))

Highlighted
New Contributor
Posts: 543
Registered: ‎10-27-2015

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
Posts: 2
Registered: ‎09-23-2016

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

[ Edited ]

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

New Contributor
Posts: 543
Registered: ‎10-27-2015

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

[ Edited ]

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