topic Re: Rolling sum of date ranges (recurring costs with start & end date) in DAX Commands and Tips

Rolling sum of date ranges (recurring costs with start & end date)

MatevzP — Wed, 15 May 2024 08:05:08 GMT

Hello,

I did a simple rolling sum for invoices, but that was easy because i have a single date for each invoice.
It looks something like this

SUM Rolling Costs to date =

CALCULATE( [SUM Cost Invoice],

FILTER(ALLSELECTED(mp_projectcost),

mp_projectcost[Date Issued] <= MAX(mp_projectcost[Date Issued])))

But now i'm facing a challange where i have to calculate a rolling (cumulative) sum for date ranges.
We have projects that run in parallel. With the Start and End date i get number of working days and dividing project budget by working days i get an average daily cost of a project.

Example from gantchart

How would i use start & end date of projects with daily average cost of each and calculate a rolling sum?

As of now, this is all i have:

This issue is well beyond me.
Help, please 😩

Re: Rolling sum of date ranges (recurring costs with start & end date)

xifeng_L — Wed, 15 May 2024 08:13:04 GMT

Hi MatevzP ,

What is your expected result? And can provide some sample data or pbix file?

Re: Rolling sum of date ranges (recurring costs with start & end date)

MatevzP — Wed, 15 May 2024 08:16:40 GMT

Hi,

I would like to create an S-curve (primitive one). So everyday, i'd like to sum up everage daily costs of all projects ("PO Budget per day" calculation from the screen shot).

I don't have sample data as it's confidential, but if needed, i could create something.

Re: Rolling sum of date ranges (recurring costs with start & end date)

xifeng_L — Wed, 15 May 2024 09:06:38 GMT

Hi MatevzP ,

I modelled some of the data myself, not sure if I can reproduce your scenario but you can refer to it.

Demo - Rolling sum of date ranges.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Rolling sum of date ranges (recurring costs with start & end date)

MatevzP — Wed, 15 May 2024 12:52:18 GMT

xifeng_L
Firstly, thank you for your effort and submitted proposal! Appreciate it very much!

I'm almost there. What you coded is a great calculation that gives me daily consumption. I think it doesn't calculate a rolling sum. I managed to get it with additional DAX formula, but the last hurdle i have is, i would like the chart to stop at today (i have created a new table with calendar up-to-today). As of now, the chart goes on even if no data is inside. I would like to use my dynamic calendar table that goes up to today, or that the rolling sum function (line chart) stops with the last available data, even better...

Demo - Rolling sum of date ranges.pbix

Could you help me out with that as well? 🙂

Thank you!

Re: Rolling sum of date ranges (recurring costs with start & end date)

MatevzP — Wed, 15 May 2024 13:00:52 GMT

In the meantime, i'll use a rimitive solution and put a date filter on the report, so we'll adjust manually the range.
It would be nice to have DAX do it for us automatically, but that will work as well.

Re: Rolling sum of date ranges (recurring costs with start & end date)

xifeng_L — Wed, 15 May 2024 14:21:02 GMT

Hi @MatevzP ,

Sorry, I misunderstood your needs and it turns out that there is also a need for cumulative.

You can try below cumulative measure.

# Option 1

Rolling cost 1 = IF([daily average cost]<>BLANK(), CALCULATE( [daily average cost], FILTER( ALLSELECTED('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) ) ) // Option 1 , This method may has flaws, such as if the value of a date in the middle is empty, that date will be ignored

# Option 2

Rolling cost 2 = VAR TempTable = CALCULATETABLE( ADDCOLUMNS(VALUES('Calendar'[Date]),"Daily Cost",[daily average cost]), ALLSELECTED() ) VAR LatestDate = MAXX(FILTER(TempTable,[Daily Cost]<>BLANK()),'Calendar'[Date]) VAR CurDate = MAX('Calendar'[Date]) RETURN IF(CurDate<=LatestDate, SUMX( FILTER(TempTable,'Calendar'[Date]<=CurDate), [Daily Cost] ) )

Demo - Rolling sum of date ranges.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: Rolling sum of date ranges (recurring costs with start & end date)

MatevzP — Wed, 15 May 2024 15:08:40 GMT

OK, xifeng_L , we are almost there, 90% what was desired.
I am sorry for nagging you so much.

But we have a discrepancy with the calculation. We have omitted weekends when calculating "Average daily cost", but we display weekends in linechart and they are being accounted for in Rolling cost dax, hence the rolling (cumulative) cost is not matching the actuall SUM of all costs.

I altered by using DATEDIFF function, that way we count also weekends for calculation, but a preferable way would be to also omitt weekends from the rolling calculation.

What i have so far is a column that denotes if it's a weekend or not. But i don't know how to incorporate to your "rolling cost2" dax code 😬

Is that someth that is easily acchieved? If it's not an easy one, counted weekends will also work.
Thank you so far!

Re: Rolling sum of date ranges (recurring costs with start & end date)

MatevzP — Wed, 15 May 2024 15:19:50 GMT

Nevermid. No dax wizardry needed! I simply filter the page with "isWeekday"...!

Thank you so much for your help!

😊

Re: Rolling sum of date ranges (recurring costs with start & end date)

xifeng_L — Wed, 15 May 2024 15:24:49 GMT

Good idea 👍