Help with Running Totals

[Userpath77]

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

        )

    )

)

[Dangar332]

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

 

[Userpath77]

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.

[Dangar332]

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]

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.

[Userpath77]

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