cancel
Showing results for
Did you mean:
Frequent Visitor

## Unexpected Running Total

How do I create a running (cumulative) sum of column C if column C = Column A divided by Column B. The problem seems to be that whenever a YTD function or Calculate with <= dates, it recalculates the YTD value for Column A and YTD for Column B then divides them, which in this case is not the same as a running sum of Column C

extras: Column A is a measure with SUM([Field Name]), Column B is measure with SUM([Field Name]).

 Date Column A Column B Column C 1/2020 75 4596 1.63% 2/2020 66 4596 1.44% 3/2020 68 4612 1.47% 4/2020 53 4595 1.15% 5/2020 46 4553 1.01% 6/2020 88 4494 1.96% 7/2020 76 4437 1.71% 8/2020 83 4406 1.88% 9/2020 74 4394 1.68% 10/2020 84 4620 1.82% 11/2020 87 4841 1.80% 12/2020 75 4823 1.56%
1 ACCEPTED SOLUTION
Super User IV

@jbajon , Try a measure like

calculate(sumx(values(Table[date]), calculate(divide(sum(Table[A]),Sum(Table[B])))), filter(AllSelected(Table), Table[date] <=max(Table[date])))

Proud to be a Super User!

4 REPLIES 4
Super User II
_Test 2 =

var a = CALCULATE(SUM('Table'[Column A]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))
var b = MAXX('Table',MAX('Table'[Column B]))
Return
DIVIDE(a,b)

Proud to be a Super User!

Frequent Visitor

Hi FarhanAhmed,

Thank you for your response. When attempting your solution I was getting an error message that MAX() only accepts column references where [Column B] is actually a measure in this example.

Super User IV

@jbajon , Try a measure like

calculate(sumx(values(Table[date]), calculate(divide(sum(Table[A]),Sum(Table[B])))), filter(AllSelected(Table), Table[date] <=max(Table[date])))

Proud to be a Super User!

Frequent Visitor

Hi amitchandak,

Thank you for your reponse. I was able to alter your example a bit to get to my final working solution. The only difference I forgot to mention on my actual project that the Year and Month are two different fields (rather than combined into one field)  for formatting reasons, so I added an additional filter condition so that the running value restarted each year.

Here is what ultimately worked for me:

calculate(sumx(values('DateTable'[FullDate]), [Column_C]), filter(allselected('DateTable'), 'DateTable'[FullDate_Year] = max('DateTable'[FullDate_Year]) && 'DateTable'[FullDate_Month#] <=max('DateTable'[FullDate_Month#])))

Announcements