Running total on measures

[SajjadMousavi]

Hi.

I have a measure with this code:

Net Sales = CALCULATE(SUM(MainParamCalulations[ParamSum]), MainParamCalulations[Code] = "Sales")

MainParamCalulations is a table with a date field. I need to calculate its running total "as a measure" which uses "Net Sales" measure inside, becuase:

1. I have 21 (and increasing) measures like this, some of them are directly calculated via MainParamCalulations and some of them are calculated based on other measures
2. I don't want to change formula of running total measures when formula of primary measures are changed
3. Measure which are calculated based on other measures cannot be a calculated column in MainParamCalulations
4. MainParamCalulations itself doesn't have a column for measures which are directly calculated based on it (it has a Code column as seen in the above formula), hence adding a lot of calculated columns to it is not a good idea IMO

I have already tested this with no luck (it works exactly like Net Sales):

RT Net Sales = SUMX(FILTER(MainParamCalulations, MainParamCalulations[TransDate] <= MAX(MainParamCalulations[TransDate])), [Net Sales])

Any help is greatly appreciated.

[SajjadMousavi · Accepted solution]

Finally found a way to do this, of course probably not optimized, but works. There are steps:

1. Created a summarized table based on MainParamCalulations to extract existing dates, along with a bidirectional 1-to-many relationship with MainParamCalulations on TransDate:

RunningTotals = SUMMARIZE(MainParamCalulations, MainParamCalulations[TransDate], MainParamCalulations[Year], MainParamCalulations[Month], MainParamCalulations[Day])

2. For each measure, added a calculated column with this formula, which uses primary measure inside:

Net Sales = SUMX(FILTER(MainParamCalulations, AND(MainParamCalulations[TransDate] <= EARLIER(RunningTotals[TransDate]), MainParamCalulations[Year] = EARLIER(RunningTotals[Year]))), [Net Sales])

3. If I use the above calculated column in a e.g. line chart, it will display correct data just in day level. Month and year values will be incorrect as sum of running totals will be shown. So I had to create another measure for each calculated column:

RT Net Sales =

VAR sm = SELECTEDVALUE(RunningTotals[Month])

VAR sd = SELECTEDVALUE(RunningTotals[Day])

VAR my = CALCULATE(MAX(RunningTotals[TransDate]), RunningTotals[Year] = SELECTEDVALUE(RunningTotals[Year]))

VAR mm = CALCULATE(MAX(RunningTotals[TransDate]), RunningTotals[Year] = SELECTEDVALUE(RunningTotals[Year]) && RunningTotals[Month] = sm)

VAR md = CALCULATE(MAX(RunningTotals[TransDate]), RunningTotals[Year] = SELECTEDVALUE(RunningTotals[Year]) && RunningTotals[Month] = sm && RunningTotals[Day] = sd)

RETURN IF(ISBLANK(sd), IF(ISBLANK(sm), CALCULATE(SUM(RunningTotals[Net Sales]), RunningTotals[TransDate] = my), CALCULATE(SUM(RunningTotals[Net Sales]), RunningTotals[TransDate] = mm)), CALCULATE(SUM(RunningTotals[Net Sales]), RunningTotals[TransDate] = md))

The above code returns correct value for each level of date hierarchy. This measure can be used in e.g. line charts.
The problem with the above approach is that measures based on other measures should have formula re-written in form of calculated measures, as all of them cannot be summarized. For example, if a measure contains division, we should sum dividend and divisor first, then divide them. We cannot sum individual quotients for e.g. days or months. But in my case, this approach is the best one possible.

[amitchandak]

@SajjadMousavi , join you table with Date table and try like

Cumm Sales = CALCULATE([Net Sales],filter(all('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm Sales = CALCULATE([Net Sales],filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

use column from date table in visual

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[SajjadMousavi]

Hi.

Thanks for your reply. Tested it, no luck. It returns the exact value as Net Sales. I re-wrote it as:

RT Net Sales = CALCULATE(SUMX(MeasureTable, [Net Sales]), MainParamCalulations[TransDate] <= MAX(MainParamCalulations[TransDate]))

and

RT Net Sales = CALCULATE(SUMX(MeasureTable, [Net Sales]), DimDate[DateKey] <= MAX(DimDate[DateKey]))

MeasureTable is a table containing all measures. But these also return the same values as Net Sales. I think the problem is passing filter context to measure calculation.