0

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.

jonleech
  • 461
  • 1
  • 6
  • 30

1 Answers1

1

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] )
    )
)
greggyb
  • 3,728
  • 1
  • 11
  • 32
  • so its to create a calculated field that is like a cumulative running total then later can use the calculated field for further calculations? – jonleech Jan 08 '16 at 17:10
  • 1
    Yes, you'd define as a measure in Power Pivot one of the TOTAL*TD() or a custom version based on a fiscal calendar hierarchy similar to the example I gave. – greggyb Jan 08 '16 at 18:23
  • Sorry am a newbie to Excel but if my baseline field is a week number and all like what i updated above then how do can i create the calculated field with the week number as the DimDate replacement? – jonleech Jan 10 '16 at 02:02
  • Thanks for the answer! but I just realized that I am using another calculated field to and I can't put that in as a measure. sigh. its driving me crazy – jonleech Jan 10 '16 at 02:28
  • then is there anyway of calculating a running total of a calculated field? This is the error excel gives me. "Calculation error in measure 'Date'[cumu of cumu]: The SUM function only accepts a column reference as the argument number 1." – jonleech Jan 10 '16 at 03:28
  • No need to SUM(). Just put the existing measure into the CALCULATE() sample I provided. – greggyb Jan 10 '16 at 03:43