Moving average (bespoke periods)

[dantheram]

Hi All

first post 🙂

I have the below table which i need to add a periodic average to, so i can show a rolling 13 period annual average; period 13 of 2018/19 would be the average of periods 1 to 13, moving on 1 period from that point.

I have managed to create a rolling average by using normal calendar dates but when i try to use my custom 13 period arrangement i cannot return any useable results. I am a new user so apolgise if this is really obvious but i cannot fathom it 🙂

[dantheram]

Thanks so much for this 🙂

i have followed the logic to add periodic dates which have aligned my data. I have also split the targets down to day level granularity - using a duration column to divde the period number.

I have another challenge which i think is an easy one but is confusing me, will post in a new thread.

Thanks again

Dan

[TheoC]

Great work @dantheram! Well done!

[dantheram]

Hi Theo

in response; i have arranged my data in a star schema, therefore my periods sit with mu calendar dimension, as below - 

i initially used a 'measure' placed within my fact table to create a rolling average for X days, like this:

rolling ave =

VAR NumDays = X

VAR RollingSum

Calulate(sum('Fact'[PfPi Mintues]),

DAYSINPERIOD('Calendar'[Date]),LASTDATE('Calendar'[Date])),-NumDays,DAYS))

Return

RollingSum/Numbdays

now this works fine for days, as you would expect, however i do not know how to change the measure to look at my periods 

[TheoC]

Hi @dantheram 

Thanks for that additional information. 

I am on the phone but will take a better look at test my theory in the morning,  however, if you change the DAYSINPERIOD to incorporate a function that targets the MONTHS in your calendar table, you should get pretty much the outcome you're after. 

I assume the P01...P13 in your Calendar table?

Thanks again!

Theo

[dantheram]

Yep, the above works fine in terms of Months, but my periods total 13 and do not directly align with months of the year, meaning the averages are out. I need the bit which, in DAX, translates calendar dates to my periods - this is done in the data model i'm just not sure how to make the formula cognisant of this 

Thanks so much for help thus far, its really appreacited 🙂 

[dantheram]

As you can see here, the calculated field is returning the month sum but the period sum is different 

[TheoC]

Hi @dantheram 

No need to apologise for anything. We are all learning and all at different stages of our Power BI journey 🙂

Are the Periods (i.e. P01, P02... P13) part of your Calendar / Date table?  Or are they separate?  

Also, did you use a Measure or a Calculated Column for the rolling average?  If you could provide me with the formula, the Community and / or I may be able to assist in adjusting it slightly to get you the outcome your after.

Thanks in advance.

Theo 🙂

[dantheram]

I think my problem is traceable back to my data model, i've taken out the connections to the 'target 21_22' table and now understand there's no available 1 to many relationship, with my targets defined at a period level by category and location manager and not at a daily level as with the other fact table.

I guess the question is now more a case of how do i join the targets to the other fact table, in such a way it enables me to use the calendar dimension when reporting

Here is my model - 

[TheoC · Accepted solution]

@dantheram thanks for the additional information!  Makes complete sense.

A couple of things, one way to achieve what you want to get the dates aligned to the respective periods you want is using the below and adapting it to your table name and to your period start date:

NewPeriod =

VAR _NewPeriod = 'Table'[Date]
VAR _Year = YEAR ( 'Table'[Date] )
VAR _P01Start = DATE ( _Year , M , D )
VAR _P02Start = DATE ( _Year , M , D )
VAR _P03Start = DATE ( _Year , M , D )
VAR _P04Start = DATE ( _Year , M , D )
VAR _P05Start = DATE ( _Year , M , D )
VAR _P06Start = DATE ( _Year , M , D )
VAR _P07Start = DATE ( _Year , M , D )
VAR _P08Start = DATE ( _Year , M , D )
VAR _P09Start = DATE ( _Year , M , D )
VAR _P10Start = DATE ( _Year , M , D )
VAR _P11Start = DATE ( _Year , M , D )
VAR _P12Start = DATE ( _Year , M , D )

RETURN

    SWITCH ( TRUE ( ) ,
        _NewPeriod < _P02Start , "P01" ,
        _NewPeriod < _P03Start , "P02" ,
        _NewPeriod < _P04Start , "P03" ,
        _NewPeriod < _P05Start , "P04" ,
        _NewPeriod < _P06Start , "P05" ,
        _NewPeriod < _P07Start , "P06" ,
        _NewPeriod < _P08Start , "P07" ,
        _NewPeriod < _P09Start , "P08" ,
        _NewPeriod < _P10Start , "P09" ,
        _NewPeriod < _P11Start , "P10" ,
        _NewPeriod < _P12Start , "P11" ,
        _NewPeriod < _P13Start , "P12" ,
        "P13" )

Change the "M" and "D" in the VARs to a numeric month (i.e. 1 will be Jan) and D to the day of the respective month (i.e 1 to 31).

Add this as a Calculated Column and you will be able to use the PXX to get your output. Below is a solution I put together for a separate post and it was to do with unusual Quarterly Start Dates 🙂