Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
So Far I have this DAX to work out the blue line:
Solved! Go to Solution.
Reached a solution by following this thread :
Reached a solution by following this thread :
@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.
Tried to share but it wouldn't let me. Here's all I have:
BI Project ID | Saving Start Date | Cost Savings per Month | Months Of Saving |
BI_02 | 22/01/2021 | 200 | 11 |
BI_07 | 20/05/2021 | 900 | 7 |
BI_04 | 03/05/2021 | 1,200.00 | 7 |
BI_05 | 07/04/2021 | 500 | 8 |
BI_06 | 01/03/2021 | 1,400.00 | 9 |
BI_08 | 23/01/2021 | 600 | 11 |
BI_01 | 01/01/2021 | 1,500.00 | 12 |
BI_03 | 03/02/2021 | 800 | 10 |
BI_09 | 13/05/2021 | 845 | 7 |
BI_10 | 10/06/2021 | 600 | 8 |
BI_11 | 01/07/2021 | 2,450.00 | 6 |
BI_12 | 12/08/2021 | 200 | 15 |
BI_13 | 03/09/2021 | 150 | 12 |
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.
This is the same as the blue line in below screenshot, just need to replicate the green line to make it work.
If I go for another datesytd based on already done calculation I'm getting the same result.
Thank you!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |