Running Total On A Calculated Measure

[Anonymous]

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]),
FILTER(
ALLSELECTED(FV_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]),
FILTER(
ALLSELECTED(FV_Refurb_Revenue),
FV_Refurb_Revenue[QC Complete DateTime] <= MAX(FV_Refurb_Revenue[QC Complete DateTime])
)
)

Any help much appreciated..

[dax_bee]

Hi David,

Did you get a resolution to your problem (running total using a measure), as I have the same problem?

Thanks In Advance.

[mellas_k]

Hi @Anonymous

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.

[v-huizhn-msft]

Hi @Anonymous,

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.

Best Regards,
Angelia

[Anonymous]

Hi Angelia,

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.

David.

[v-huizhn-msft]

Hi @Anonymous，

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?

Best Regards,
Angelia

[Anonymous]

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.

[v-huizhn-msft]

Hi @Anonymous,

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.

Best Regards,
Angelia