Forum Discussion
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...
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.
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.
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.