Calculate Column Iterating Subtotal Column

[Anonymous]

Hello All!

I am trying to calculate a column of the subtotal of revenue by a year-month column:

Yr-Mo    Revenue      SubTotal Revenue
2021-01  1,000,000    1,000,000
2021-02  1,000,000    2,000,000
2021-03  1,000,000    3,000,000
2021-04  1,000,000    4,000,000
2021-05  1,000,000    5,000,000

I've tried a number of functions and always returns 5,000,000 for every row.  

Thanks in advance!

[v-luwang-msft · Accepted solution]

Hi  @Anonymous ,

Use the following measure:

revenuetotal = CALCULATE(SUM('Table'[Revenue]),FILTER(ALL('Table'),'Table'[Yr-Mo]<=MAX('Table'[Yr-Mo])))

then create a new column:

Wish it is helpful for you!

Best Regards

Lucien

[Anonymous]

Awesome!  Thanks; didn't even know  that was a thing!

[Fowmy]

@Anonymous 

Try like this:

Sub Total Revenue = 

CALCULATE(
    SUM(Table3[Revenue   ]),
    FILTER( ALL(Table3[Yr-Mo   ]), Table3[Yr-Mo   ] <= MAX(Table3[Yr-Mo   ] ))
)

[Anonymous]

@Fowmy thank you for the quick reply.  It is returning the same value in the row for revenue.

[Fowmy]

@Anonymous 

Can you share a screenshot of the result?

[Anonymous]

@Fowmy can you send your results as well?  This is really tricky.

[Fowmy]

@Anonymous 

Please find attached the file.

[Anonymous]