Unexpected Running Total

[jbajon]

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%

[FarhanAhmed]

_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)

[jbajon]

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.

[amitchandak · Accepted solution]

@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])))

[jbajon]

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#])))