Show accumulated total in Column when month selected

[DanteL]

I have two visuals:-

1) MatrixTable has a column called Budget
2) Another chart (Date) shows the Budget by Year, Quarter and Month Number (1, 2, 3....12)

I would like to add a column to MatrixTable that shows the Budget up to the month selected in the Date chart, and if no month is selected in the chart, the Budget has to show data up to the latest month.

How do i do this using a measure ?

So ChatGPT came up with the below DAX

Budget Up to Selected Month = 
VAR SelectedMonth = MAX('Date'[Month Number])
VAR LatestMonth = MAX('Date'[Month Number])
VAR FilteredBudget = 
    CALCULATE(
        SUM('MatrixTable'[Budget]),
        FILTER(
            ALL('Date'),
            'Date'[Month Number] <= SelectedMonth || ISBLANK(SelectedMonth)
        )
    )
RETURN
    IF(ISBLANK(FilteredBudget), BLANK(), CALCULATE(SUM('MatrixTable'[Budget]), 'Date'[Month Number] = LatestMonth))

The code above works only when i select an individual specific month, but not multiple month selections or even without any selection. Eg. below...

Anyone has ideas how to solve this please ? Much appreciated ! Thanks !

[DanteL]

It wasn't the solution that i was looking for unfortunately. I managed to solve the issue eventually using another method. Thanks.

[v-jianboli-msft]

Hi @DanteL ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[Thejeswar]

Hi @DanteL ,

The Above DAX that you have given is using a filter with "=" operator. So it will always give the data for the month selected.

I would write a DAX like the one below

Sales = 
 VAR MAXMONTH = SELECTEDVALUE('Date'[MonthNo])
 RETURN
 IF(NOT(ISFILTERED('Date'[MonthNo])), SUM(tblInternetSales[SalesAmount]),
 CALCULATE(SUM(tblInternetSales[SalesAmount]), 'Date'[MonthNo] <= MAXMONTH))

Note: When using the above measure in your visual, you should not use the Date Column in the visual

The Above DAX works well both when a Month is selected and when Nothing is selected. But it won't work when Multiple Months are selected. When we are getting data until a particular month, multi selection might not make sense. Hence sharing this DAX with you

The Screenshot of the Output is given below for your reference

When one month is selected

When Nothing is selected