Running Total Filtering

[RI]

Hi,

Have created table containing month cummlative budget vs. cummlative actual.

Below is table Achv. is for Apr-Sep (6 Months)

above is working absolutely fine till we select filter ,  results when we selecte a particular category in slicer

 it is showing till sep only which is ok for actual but we want budget nos. for complete year,

So running totals are adjusted to Month of Actual data instead full year,

Pls guide  

[Anonymous · Accepted solution]

Hi @RI ,

I think your table should look like as below.

Then add a Calendar table.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 04, 01 ), DATE ( 2024, 03, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthSort", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" )
)

Data model:

Measure:

Actual = CALCULATE(SUM('Table'[Acutal]),FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

Budget = CALCULATE(SUM('Table'[Budget]),FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))

Result is as below.

Best Regards,
Rico Zhou

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

[andhiii079845]

You have to upload some example table of your underlaying data. I think you will need a second table for the date to

get the complete year in the table