Forum Discussion
Cumulative Total in Calculated Column
Hello! I have sample table below and I tried to make a calculated column to compute for the cumulative total per Count grouping per month.
Below is my screen shot of my PBIX where the results do not show correctly while second picture shows he expected results I need.
I already tried using ALLSELECTED instead of ALL and EARLIER instead of MAX, but it still doesn't work.
Cumul =
CALCULATE( SUM('Table'[SubTotal_Vol]),
FILTER(ALL('Table'[Date]),'Table'[Date] <= MAX('Table'[Date])))
PBIX
Expected Results
Not sure if this info helps, but my Date column is just a combination of Month and Year and then converted data type into Date.
I was able to use this formula:
Cumul = CALCULATE(SUM('Table'[SubTotal_Vol]), FILTER(ALLEXCEPT('Table','Table'[Merged]), 'Table'[Date]<= EARLIER('Table'[Date]) && 'Table'[Merged] <= MAX('Table'[Merged])))
5 Replies
Hi newgirl - I think you need
VAR _Date = MAX([Date])
ALLSELECTED()REMOVEFILTERS([Date])
FILTER ( [Date] <= _Date)
I was able to use this formula:
Cumul = CALCULATE(SUM('Table'[SubTotal_Vol]), FILTER(ALLEXCEPT('Table','Table'[Merged]), 'Table'[Date]<= EARLIER('Table'[Date]) && 'Table'[Merged] <= MAX('Table'[Merged])))Hi! I am wondering, what is the Merged variable that you are using in your solution?
Hello! The Merged column is the merging of 2 columns in which how I grouped them. For example, I consider INLAND100 as one group, RETAILER327 as another, which helped compute for the cumulative for that group.
Hello all,
I was wonderfing if there is a way to get the cumulative column by employee and/or project number? RIght now I have the cumulative column which generates a sum much greater than it should be but when it is in measure format it works correctly. Essentially, I am trying to cumulative hours per employee instead of the whole entire company.
Let me know what you may have to help.
Thank you.
