Forum Discussion

RI's avatar
RI
Frequent Visitor
2 years ago
Solved

Running Total Filtering

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  

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • andhiii079845's avatar
    andhiii079845
    Solution Sage
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.