Dynamic 12-Month Rolling Cumulative Totals Based on Selected End of Month in PowerBI bar chart

[Anonymous]

 having some trouble with PowerBI I have a date dimension with an "End of Month" column and a fact table that contains records for each end of the month.

I would like to display the following in a single graph:

On the X-axis: the "End of Month" for the last 12 months, based on the selected month filter. On the Y-axis: the cumulative total for each month, calculated over the previous 12 months. For example, if I select June 2024, the graph will display months from July 2023 to June 2024 on the X-axis, and for June 2024, the cumulative total will represent the sum from July 2023 to June 2024. Additionally, for May 2024, it will show the cumulative total from June 2023 to May 2024, for April 2024 the cumulative total from May 2023 to April 2024, and so on, back to July 2023.

I created the following measures:

_NumberOfDepartures = 
CALCULATE(
    COUNTROWS(Fact_Termination),
    Fact_Termination[EmployeeClass] = "Employee",
    Fact_Termination[PermanentTemporary] = "Permanent"
)

Cumul12months = CALCULATE(
    [_NumberOfDepartures],
    DATESINPERIOD(
        'Dim_Date'[End of Month], 
        MAX('Dim_Date'[End of Month]), 
        -12, 
        MONTH
    ),
    REMOVEFILTERS('Dim_Date')
)

This solution currently works only if I manually select each month one by one in the slicer. However, I need it to be dynamic: when I select a single month, it should automatically calculate the rolling 12-month cumulative total for that month, and for each of the previous 12 months.  When I try to put the date field from my fact table on the X-axis, I do get the 12 months, but in the values, I don't see the cumulative totals

I also tried another approach by creating a cumulative table in Power BI using the SUMMARIZE function. However, I ran into issues because I need to apply additional filters on other fields and perform calculations involving different tables. This is why I need the solution to be dynamic.

Can you help me with this, please?

[Anonymous · Accepted solution]

Hi, @Anonymous 

Thanks for bhanu_gautam's method. You can create another date table that is unrelated to the fact table to be used as a slicer, and use Flag measure to filter the x-axis of the visual object.

date flag = 
VAR _slicer =
    IF ( ISFILTERED ( Slicer[yyyy-mm] ), MAX ( Slicer[yyyy-mm] ), 0 )
VAR _tableSelectedDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[yyyy-mm] = _slicer )
    )
VAR _startDate =
    EOMONTH ( _tableSelectedDate, -12 ) + 1
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[End of Month] ) >= _startDate
            && SELECTEDVALUE ( 'Table'[End of Month] ) <= _tableSelectedDate,
        1,
        0
    )


Rolling Cumulative Totals = 
VAR _date =
    SELECTEDVALUE ( 'Table'[End of Month] )
VAR _startDate =
    EOMONTH ( _date, -12 ) + 1
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] >= _startDate
                && 'Table'[Date] <= _date
        )
    )
RETURN
    _sum

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Thank you for the solution, but it couldn't work in my case because I had multiple filters on different dimensions, and adding a slicer wouldn’t have solved the issue. However, I adapted the idea to fit my structure by using a new table called dim_date_Relative.

In Power BI, my slicer is applied to Dim_Date, and in my calculations, I use both Dim_Date and dim_date_Relative to ensure accuracy. The calculations are based on the relative date, which brings in the last 12 months from the selected date. I also added a column to dim_date_Relative called isInLast12Months, which indicates whether a date falls within the last 12 months. In the graphic, I use the EndOfMonth from dim_date_Relative on the X-axis.

[bhanu_gautam]

@Anonymous , Try below measure

Cumul12months =
VAR SelectedMonth = MAX('Dim_Date'[End of Month])
RETURN
CALCULATE(
[_NumberOfDepartures],
DATESINPERIOD(
'Dim_Date'[End of Month],
SelectedMonth,
-12,
MONTH
)
)

Ensure your date dimension table is marked as a date table:

Create a visual:

Add the End of Month column from your date dimension table to the X-axis.
Add the Cumul12months measure to the Y-axis.
Add a slicer for the month selection:

Add a slicer visual to your report.
Use the End of Month column from your date dimension table in the slicer.
Set the slicer to single select mode:

Select the slicer visual.
Go to the Format pane.
Under Selection Controls, enable Single select.

[Anonymous]

@bhanu_gautam unfortunatly it doesn't work I only get the month selected and value of the mesure is not returning the right number

my purpose is to get this visual by only selecting the last month: