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
kasiaw29
Resolver II
Resolver II

Running Totals, YTD, Cumulative?

Hi community, 

 

I'm looking for clarity on DAX to get two measures. 

I have a DateDim table build and a Fact table that holds data on business savings (see dummy data below). It gives a project, saving start date, saving value and number of months savings are lasting for. 

 

I need to get a running total for each month, and then a running total of that total if that makes sense? 

See below: 

2021-02-08_08-52-35.jpg

So Far I have this DAX to work out the blue line:

Cummulative Total Savings = CALCULATE([Cummulative savings], DATESYTD(DateDim[Date]))
 
Calc for [Cummulative savings] - 
Cummulative savings = CALCULATE(SUM('FactTable'[Cost Savings per Month]),
        FILTER('FactTable',
           'FactTable'[Saving Start Date] <= MAX(DateDim[Date])
           && ('FactTable'[Saving End Date] > MAX(DateDim[Date]))))
 
I need help getting the green line, I tried repeating what I did with cummulative total savings but it's not giving me the right figures. 
 
EDITED: 
Reason for above cumulative measure is becasue savings run for cerain periods of time, for example 7 months or 6 month or 8 months. So given the months of savings I was able to work out the end date. Then I run a sum of savings per month based on that and then did a cumulative measure to see each month, at the end of the motn how much I was saving. 
 
Now I just need a cumulative of that cumulative which is what green line shows in excel. 
 
Thanks,
Kasia 
1 ACCEPTED SOLUTION
kasiaw29
Resolver II
Resolver II
6 REPLIES 6
kasiaw29
Resolver II
Resolver II

amitchandak
Super User
Super User

@kasiaw29 , first row seems monthly total without any other filter then time

like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

or

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]),filter(allselected(Date), Date[Month year] = Max(date[Month year])))

 

Cummulative  example with and without date table

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))


Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date])))

Not quite what I'm after. I already have a cumulative total that works ok, I'm after cumulative of that cumulative if it makes sense? 

 

Thanks! 

@kasiaw29 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

@amitchandak 🙂

Tried to share but it wouldn't let me. Here's all I have: 

 

BI Project IDSaving Start DateCost Savings per MonthMonths Of Saving
BI_0222/01/202120011
BI_0720/05/20219007
BI_0403/05/20211,200.007
BI_0507/04/20215008
BI_0601/03/20211,400.009
BI_0823/01/202160011
BI_0101/01/20211,500.0012
BI_0303/02/202180010
BI_0913/05/20218457
BI_1010/06/20216008
BI_1101/07/20212,450.006
BI_1212/08/202120015
BI_1303/09/202115012

 

So as you can see savings start at different dates through the year and last x number of months. I have this chart that displays that nicely. 

kasiaw29_0-1612954918542.png

 

This is the same as the blue line in below screenshot, just need to replicate the green line to make it work.

 

2021-02-08_08-52-2.jpg

If I go for another datesytd based on already done calculation I'm getting the same result.

 

Thank you!

@Greg_Deckler maybe some ideas?

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.

Top Solution Authors