Display Last 12 Months Data as per Month Slicer for Rolling 12 Month Measure

[Anonymous]

Display Rolling 12 Month Measure Data for Past 12 Months as per Selected Month on Slicer 

Hi,

Please refer to above link to Power BI .pbix file.

I have a measure named 'Terminations (Rolling 12 Months)' which is calculating rolling 12 month data. The issue is  that when I select Year and Month from the slicer, it only shows one month data which is selected on slicer. 

However, it should show last 12 months data before the month selected on slicer. For example, if I select '2022' year and 'May' month on slicer, then data should appear from April 2021 to May 2022 in all visuals.

Update, 25th May 2022: In case above link is restricted to access .pbix file, please refer below information so that you will have more details to the context.

Tables: There are two main tables - Actions and Cal.

'Actions' Table: Simple data with 5 columns - Date, DateID, Action, Action_Reason and RowVal. RowVal is the fact field and rest of the fields are dimensions. Please refer below screenshot:

Cal Table: Cal is a 'Date' table with many date-specific relevant fields (including fiscal period, month, month-year, etc. correspoinding to calendar month). However, I am trying my logic on Calendar Date related fields to start with. Please refer below two screenshots of Cal table:

12 Month Rolling Measure: Below is the formula used for calculating 12 month rolling Termnations data (based on RowVal):

Terminations (Rolling 12 Months) =

CALCULATE(SUM('Actions'[RowVal]),

DATESBETWEEN('Cal'[day],

NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Cal'[day]))),

LASTDATE('Cal'[day])))

This measure is giving correct data for rolling 12 month numbers - see below:

Slicer / Filter for Power BI Report: Power BI report is based on two time-based slicer to start with - one is Year and another is Month (full month name). And user can select only one year and one month at a time.

Issue: The issue is that we want to show data for past 12 months from the selection of Year and Month on slicer. For example, if I select '2022' year and 'May' month on slicer, then data should appear from April 2021 to May 2022 in all visuals. But is only give May 2022 data - refer screenshot below:

Kindly help.

Thanks and Regards,

Rohit Sharma

[lbendlin]

"it should show last 12 months data before the month selected on slicer. For example, if I select '2022' year and 'May' month on slicer, then data should appear from April 2021 to May 2022 in all visuals"

That's 13 months.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

First, please do not create any relationship between Actions and Cal table.

Then update the formula of measure [Terminations (Rolling 12 Months)] as below:

Terminations (Rolling 12 Months) = 
VAR _selyear =
    SELECTEDVALUE ( Cal[cal_year] )
VAR _selmonth =
    SELECTEDVALUE ( 'Cal'[cal_month] )
VAR _startmonth =
    IF (
        VALUE ( _selmonth ) = 12,
        VALUE ( _selyear & "01" ),
        VALUE (
            _selyear - 1
                & IF (
                    1 + VALUE ( _selmonth ) < 10,
                    "0"
                        & ( 1 + VALUE ( _selmonth ) ),
                    1 + VALUE ( _selmonth )
                )
        )
    )
VAR _endmonth =
    VALUE ( _selyear & _selmonth )
VAR culvalue =
    CALCULATE (
        SUM ( 'Actions'[RowVal] ),
        FILTER (
            ALLSELECTED('Actions') , 'Actions'[Date]<=SELECTEDVALUE('Actions'[Date])&&
            VALUE ( YEAR ( 'Actions'[Date] ) & FORMAT ( 'Actions'[Date], "mm" ) )
                >= VALUE ( _startmonth )
                && VALUE ( YEAR ( 'Actions'[Date] ) & FORMAT ( 'Actions'[Date], "mm" ) ) <= _endmonth
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Actions'[Date] )
            < DATE ( LEFT ( _startmonth, 4 ), RIGHT ( _startmonth, 2 ), 1 )
            || SELECTEDVALUE ( 'Actions'[Date] ) > DATE ( _selyear, VALUE ( _selmonth ), 1 ),
        BLANK (),
        culvalue
    )

Finally, create a table visual and apply a visual-level filter with the condition([Terminations (Rolling 12 Months)] is not blank)

Best Regards

[Anonymous]

Hi Yinguinr,

Thanks for the solution. It seems working fine except one thing - I noticed that for the first month in a 12 month range, it shows the actual value for that month and not aggregated 12-month value. Please see below:

In above screenshot, value for 12/01/2020 cannot be 0 as there is data before this month. So it should have aggregated 12 month numbers for each month (past 11 months and 12th selected month).

Second thing I want to confirm about Dates in Actions table. I noticed that you took first day of every month. So please confirm if it is necessary to have date as first day of each month or it could be any date?

Thanks again for looking into this.

Kind Regards,

Rohit Sharma.

[Anonymous]

Hi @Anonymous ,

I am calculating the aggregated value of column RowVal, which has a value of 0 between 2020/12/1 and 2021/4/1, so the aggregated value for this period will only be 0. You can refer to the formula of measure inside to apply it to your report.

Also, the value of date field in Actions table can be any date.

Best Regards

[Anonymous]

Hi @lbendlin ,

Thanks for your reply. Sorry if I confused you, but my query was different. Let us take an example of screenshot you shared, it shows that 11th month of year 2021 (Nov 2021) is selected in slicer - so it is showing data from Dec 2020 to Nov 2021, which is correct.

However, in the screenshot, please notice that 12 month rolling numbers for Dec 2020 (12/01/2020) is 0. So what I was saying is that Dec 2020 should have aggregate value of 12 months from Nov 2019 to Dec 2020 and same for each month in list. So each month row will have 12 month rolling value and not just the visible months aggregate. Please refer below screenshot which shows that Dec 2020 (12/01/2020) in your screenshot should have 8 and not 0.

Data

So I think measure needs to be modified for that logic if you can help with that.

Thank you,

Rohit Sharma.