Reply
Userpath77
Helper II
Helper II

Help with Running Totals

Hi All 

 

I have a table called month_vals which just has 12 months (field is called Month Short) and an FY Order column which numbers the months starting with Oct as 1, Nov as 2, etc.. It serves as the x-axis on my line combo chart. It is joined to another table called Commitment_Docs. The Commitment_Docs table has the transactions I need to turn into a cumlative running total; this table also has a date field. Power Bi generated the below formula for the running balance. It works except it takes the amount for the current month and puts it in the future months. I need it to stop at the current month. Does anyone have a solution on how to modify?

 

CALCULATE(
    SUM('Commitment_Docs'[Commitment Amt]),
    FILTER(
        CALCULATETABLE(
            SUMMARIZE('Month_Vals', 'Month_Vals'[FY Order], 'Month_Vals'[Month Short]),
            ALLSELECTED('Month_Vals')
        ),
        ISONORAFTER(
            'Month_Vals'[FY Order], MAX('Month_Vals'[FY Order]), DESC,
            'Month_Vals'[Month Short], MAX('Month_Vals'[Month Short]), DESC
        )
    )
)
5 REPLIES 5
Dangar332
Super User
Super User

hi, @Userpath77 

try below

CALCULATE(
    SUM('Commitment_Docs'[Commitment Amt]),
    FILTER( 
      allselected('Month_Vals', 
                  'Month_Vals'[FY Order],
                  'Month_Vals'[Month Short]
      ),
     'Month_Vals'[FY Order] <= MAX('Month_Vals'[FY Order])
     && 'Month_Vals'[Month Short] < MAX('Month_Vals'[Month Short])
   ) 
)

 

Thanks Dangar. When I enter yoru formula, I get a message that multiple arguments are not allowed in the ALLSELECTED function when the first argument is a table reference.

Hi, @Userpath77 

 

In above code update allselected 

 

CALCULATE(

    SUM('Commitment_Docs'[Commitment Amt]),

    FILTER( 

      allselected(

                  'Month_Vals'[FY Order],

                  'Month_Vals'[Month Short]

      ),

     'Month_Vals'[FY Order] <= MAX('Month_Vals'[FY Order])

     && 'Month_Vals'[Month Short] < MAX('Month_Vals'[Month Short])

   ) 

)

 

123abc
Community Champion
Community Champion

It looks like you are trying to create a cumulative running total for the 'Commitment Amt' column in the 'Commitment_Docs' table, based on the 'Month Short' and 'FY Order' columns from the 'Month_Vals' table. The issue you're facing is that the current formula is including future months in the cumulative total.

To stop the cumulative total at the current month, you can modify the formula by changing the condition in the FILTER function. Specifically, you want to include only the rows where the 'Month Short' and 'FY Order' values are less than or equal to the maximum values for the same columns. Here's a modified version of your formula:

 

CALCULATE(

                  SUM('Commitment_Docs'[Commitment Amt]),

                  FILTER(

                      CALCULATETABLE(

                        SUMMARIZE('Month_Vals', 'Month_Vals'[FY Order], 'Month_Vals'[Month Short]),                                                  ALLSELECTED('Month_Vals')

                 ), 'Month_Vals'[FY Order] <= MAX('Month_Vals'[FY Order])

                      && 'Month_Vals'[Month Short] <= MAX('Month_Vals'[Month Short]) ) )

 

In this modified version, I replaced the ISONORAFTER function with a condition that checks if the 'FY Order' and 'Month Short' values are less than or equal to the maximum values for the same columns. This change ensures that only the rows up to and including the current month are considered in the cumulative total.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Thanks 123abc. This is getting close. When I plugged in your changes it was showing the months of Oct, Nov and the last month of Sep. It should only show Oct and Nov. I changed your formula below just removing the <= MAX to = MAX. That change fixed my months where I just see values on Oct and Nov. The problem now is that the values are not showing cumulative, they show the incremental values for Oct and Nov. Is there a solution for this? (My minor changes removing <= show below)

 

CALCULATE(

                  SUM('Commitment_Docs'[Commitment Amt]),

                  FILTER(

                      CALCULATETABLE(

                        SUMMARIZE('Month_Vals', 'Month_Vals'[FY Order], 'Month_Vals'[Month Short]),                                                  ALLSELECTED('Month_Vals')

                 ), 'Month_Vals'[FY Order] = MAX('Month_Vals'[FY Order])

                      && 'Month_Vals'[Month Short] = MAX('Month_Vals'[Month Short]) ) )

 

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)