12 Week Rolling Average without Dates

[richardburling]

Good morning and Happy New Year, 

I have been wracking my brains for a while now on how to solve a problem. What I need to do is create a rolling 12 week average, which I seem to have done but the problem lies with the initial 12 weeks of the selected financial year. Rather then looking back at the 12 weeks in the previous financial year, its start off at week 1 and accumulates the average until it gets to week 12. Hopefully the visual below explains it better. 

Ideally, not sure if its possible in Power Bi to generate values in years that arent selected in the slicers, but I would want week 1 to be an average of the previous 12 weeks in the last financial year even if its not been selected so the user can select a year at a time rather then multiples. I have put the DAX below. 

_12W_RA =
VAR CurrentWeek = MAX(DIM_CALENDAR[WEEK_OFFSET])
RETURN
CALCULATE(
SUMX(
FILTER(
ALLSELECTED(FCT_LCHID),
FCT_LCHID[DIM_CALENDAR.WEEK_OFFSET] <= CurrentWeek
&& FCT_LCHID[DIM_CALENDAR.WEEK_OFFSET] >= CurrentWeek - 11
),
FCT_LCHID[LOST_CUSTOMER_HOURS]
)
)/12

Any help would be great. 

Thanks

Rich

[v-yifanw-msft]

Hi @richardburling ,

Based on the information you provided, for solving the problem that week 1 is the average of the first 12 weeks of the previous fiscal year, you can follow these steps:

1.Add new measure.

Measure =

AVERAGEX (

    FILTER (

        ALL ( 'Table' ),

        YEAR ( [Month Year] )

            = YEAR ( SELECTEDVALUE ( 'Table'[Month Year] ) ) - 1

            && 'Table'[Week] <= 12

    ),

    'Table'[amount]

)

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Ada Wang}

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

[richardburling]

Hi. 

Thank you for your response. 

Unfortunatley the DAX doesnt work on my dataset. The client I work for uses a financial year column which is a text data type, for example, 2023-24. They also work on a 52 week year and there isnt a unqiue column in the DIM_Calendar table that combines the Financial_Year and Week_No, like you have done in your data 'WeekNumber & Year".

Also, the value that I am looking to create a rolling 12 week average on isnt summed by the Week_No but by an unique record ID.