Forum Discussion

jbajon's avatar
jbajon
Frequent Visitor
5 years ago
Solved

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 th...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

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

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
jbajon
Frequent Visitor
5 years ago

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