Rolling average based on days and NOT sales value

[Anonymous]

Hi.

How do I create a rolling average for the last 12 months where the rolling average should be based on days and dates? 

I wanto know the monthly average of Liggetid_dage based on the registered date or calendar date(I have a calendar table as well).

Best regards,

Lisa 

[Anonymous]

Hi @Anonymous ,

I created a calendar table which has YearMonthNumber and YearMonthName.

Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2023, 12, 31 ) ),
    "YearMonthNumber", VALUE ( FORMAT ( [Date], "YYYYMM" ) ),
    "YearMonthName", FORMAT ( [Date], "YYYY-MMMM" )
)

Then create a measure to get the rolling average for the last 12 months.

a rolling average for the last 12 months =
VAR _currentday =
    MAX ( 'Calendar'[Date] )
VAR _end =
    TODAY ()
VAR _start =
    EOMONTH ( _end, -12 ) + 1
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Calendar'[YearMonthName] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            [YearMonthNumber] >= MAX ( 'Calendar'[YearMonthNumber] )
                && [Date] >= _start
                && [Date] <= _end
        )
    )
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[Liggetid_dage] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            [YearMonthNumber] >= MAX ( 'Calendar'[YearMonthNumber] )
                && [Date] >= _start
                && [Date] <= _end
        )
    )
RETURN
    IF ( _currentday >= _start && _currentday <= _end, DIVIDE ( _sum, _count ) )

Best Regards,

Stephen Tao

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

[Anonymous]

Hi @Anonymous 
Thank you for your reply. 

I just tried your equations in my data set and I think that rolling average is not what I am looking for then because this number is way too high. 
I just need a "normal" average that includes previous dates as well even if I limit the visual to 12 months - the average should be around 400-500 days. Can you help me with that?

[amitchandak]

@Anonymous , You need to have a measure like

12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

You can also consider

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc