Possible to use a running total row in powerpivot for calculation in a calculated field?

Question

If I have a pivot table and I set the row to be a running total according to date and right now i will like to use this row to create a calculated field. Is it possible?

If not then is there a formula for cumulative calculations for calculated field?

Will supply more examples if need more clarification.

I want to do something like this

week 1 2 3 4 5 6 7 8 9 10 count 20 20 21 25 26 27 28 29 21 21 cumulative count 20 40 61 86 112 139 167 196 217 238

If the week is the base field then can I create a calculated field that does something like the cumulative count? I am doing this as i need to use the cumulative count for further calculations and if i use the show values as running total it seems to me that I cant use that variable for further calculations.

Hope this helps to clarify.

greggyb greggyb · Accepted Answer · 2016-01-08T13:37:03

There are time intelligence functions built into DAX. You could use TOTALYTD(), TOTALQTD(), and TOTALMTD() if you have a proper date dimension with contiguous, non-repeating dates ranging from January 1 in the first year you have data through December 31 in the last year you have data.

If you have a non-standard fiscal calendar you can get the same effect so long as you have index fields for each time granularity of interest which are increasing over time.

CustomTotalYTD:=
CALCULATE(
    [<some measure>]
    ,FILTER(
        ALL( 'DimDate' )
        ,'DimDate'[FiscalYear] = MAX( 'DimDate'[FiscalYear] )
            && 'DimDate'[Date] <= MAX( 'DimDate'[Date] )
    )
)

Possible to use a running total row in powerpivot for calculation in a calculated field?

1 Answers