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 ,  r...
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.

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