I am trying to create a Running Sum which calculates the cumulative total of Calculated Measure.
I have done this before successfully on a Column using this pattern:
CumulativeSoldHours = CALCULATE( SUM(FV_Sold_Hours[Sold Hours]),
FV_Sold_Hours[QC Date] <= MAX(FV_Sold_Hours[QC Date])
However if I try to use the above on a Measure I cannot because the SUM function requires a Column?
This was my attempt which fails because FV_Refurb_Revenue[Actual Refurb Revenue By QC Date] is a Measure and not a Column.
CumulativeRefurbRev = CALCULATE( SUM(FV_Refurb_Revenue[Actual Refurb Revenue By QC Date]),
FV_Refurb_Revenue[QC Complete DateTime] <= MAX(FV_Refurb_Revenue[QC Complete DateTime])
Any help much appreciated..
Did you get a resolution to your problem (running total using a measure) because i have the same issue and i don't find a solution;
thanks for your help.
why do you use ALLSELECTED function in your formula, there is a slicer or filter on 'FV_Sold_Hours' table?
I think you'd better create a Calendar table and create a relationship between Calendar and your fact table.
CumulativeRefurbRev = CALCULATE( SUM(FV_Refurb_Revenue[Actual Refurb Revenue By QC Date]), FILTER( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) )
More details, please review the DAX in this article.
If this doesn't resolve your issue, please share your sample data table for further analysis.
Thanks for your response.
I am using ALLSELECTED because I have a slicer in the report, so I am happy with this bit.
There is also a Calendar table which I have named DIM_Time, however my Fact table has two dates, so I have used a measure to calculate the values from the other date field. This in itself works fine. The problem I am now having is that the examples for Running Totals seem to all involve using SUM which only works on a Field and not on a Measure.
My measure has the following expression (which works):
Actual Refurb Revenue By QC Date = CALCULATE(SUM(FV_Refurb_Revenue[Amount]),USERELATIONSHIP(FV_Refurb_Revenue[QC Complete DateTime],DIM_Time[TimeDate]))
Any further help appreciated as this has still got me stumped.
You can create a relationship between 'FV_Refurb_Revenue' and DIM_Time, then you replace SUM(FV_Refurb_Revenue[Amount]) to SUM(FV_Refurb_Revenue[Actual Refurb Revenue By QC Date]) in "CumulativeRefurbRev" measure.
In your resource table, DIM_Time table is different from Calendar table?
thanks for the response, however I cant do
SUM(FV_Refurb_Revenue[Actual Refurb Revenue By QC Date])
because Actual Refurb Revenue By QC Date is a calculated measure
This is the crux of my problem, how do you create running total on a calculated measure.
I got it. So I wish you post your sample data, and I see if I can integrate the two measure into one, rather than creating running total on a calculated measure. I am unable to reproduce your scenario without data.
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps