Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ATL_Adam
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
kcantor
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.