Forum Discussion

SajjadMousavi's avatar
SajjadMousavi
Helper II
4 years ago
Solved

Running total on measures

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 n...
Need Help
  • SajjadMousavi's avatar
    SajjadMousavi
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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-bd52912a5bd4
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's avatar
SajjadMousavi
Helper II
4 years ago

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.