Calculate the Average of the Last 8 weeks not using DAX time intelligence

[hstgeorge]

So, I have a column that is week number from 1 - 52 or current week.

I want to take the max week which would be the most current week in the data set and then use that number to calculate the average sales for the past 8 weeks excluding the current week.

So for instance current week is 39 in the column that states the week number.  I want to add up and then average the sales for weeks 30 thru 38.

I know this has to be easy but I'm struggling over here.  Any help would be appreciated.  I am assuming I would use the max function somehow and then calculate to get average sales.

Thank you.

[MFelix]

Hi @hstgeorge ,

How do you have you model setup? are you using only week/year column or do you use date?

Asking this because you can use a filter of the week and taking the 8 weeks out. however if you are using dates columns you will need to calculate the week on the measure you are doing.

But if you have a simple setup of week and year column you need to use something similar to:

Average last 8 weeks =
VAR current_week =
    SELECTEDVALUE ( Table[Week] )
VAR temp_table =
    FILTER (
        ALL ( TABLE );
        Table[Week] <= current_week - 1
            && Table[Week] >= current_week - 9
    )
RETURN
    AVERAGEX ( temp_table; Table[Column] )

Be aware that this is just a generic formula, if you want to calculate with previous years values this need to be adjusted with the year.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.