cancel
Showing results for
Did you mean:
Helper II

Calculate Column Iterating Subtotal Column

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.

1 ACCEPTED SOLUTION
Community Support

Hi  @walkerb32 ,

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

8 REPLIES 8
Community Support

Hi  @walkerb32 ,

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

Helper II

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

Super User

@walkerb32

Try like this:

``````Sub Total Revenue =

CALCULATE(
SUM(Table3[Revenue   ]),
FILTER( ALL(Table3[Yr-Mo   ]), Table3[Yr-Mo   ] <= MAX(Table3[Yr-Mo   ] ))
)``````
Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

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

Super User

Can you share a screenshot of the result?

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

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

Super User

@walkerb32

Please find attached the file.

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

Announcements

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.