Creating Cumulative Measure referencing another meausre

[vonckk]

Hello,

I could really use some help with a visual I'm working on.  I'm looking to show the actual spend, forecasted spend, monthly delta, and the cumulative delta throughout the year.  The chart below has all of the data elements except for the cumulative total.

The actual value and forecast value are a part of my base data set.  I've created a measure to track the absolute delta.  I used the below formula:

R&A Absolute Delta = abs(sum('Import - RA Financials'[Actual Value])-sum('Import - RA Financials'[Forecast Value]))

This seems to work pretty well as it is aggregating the data by months and displaying it correctly.  I'm having issues creating the cumulative total.  I was able to create one when referencing a specific column, but when I try to reference the Absolute Delta measure, it gives me an error.  Does anyone know if it's possible to reference this measure and get a cumulative total from it?

Also for bonus points, any idea on the best way to only measure the delta on months that have actual and forecasted spend?

Thanks for the help!

[Anonymous · Accepted solution]

Hi @vonckk ,

Try using SUMX() function.

Measure = ABS(SUM('Table'[value1])-SUM('Table'[value2]))

Measure 2 = SUMX(FILTER(ALLSELECTED('Table'),'Table'[date]<=MAX('Table'[date])),[Measure])

Best Regards,

Jay

[vanessafvg]

can you provide the code you used for the cummulative total? What error did you get.

[vonckk]

vanessafvg, see below:

Cumulative R&A Project Delta =

Calculate(

[R&A Absolute Delta],

Filter(

Allselected('Import - RA Financials'[R&A Period Date]),'Import - RA Financials'[R&A Period Date]<=max('Import - RA Financials'[R&A Period Date])))

This is actually not giving me an error, but is just duplicating the same line as in the original graph.  I'm not seeing any cumulation happening by date.

Thanks,

Kyle