Last 8 Weeks Relative to Slicer End Date

[mboucher_rcr]

Hi,

I'm sure someone has asked this before but I've trawled through a heap of posts and can't quite find one that works for me.

I have a date slicer with start and end dates, and I have the below measure which gives me the average sales quantity over the last 8 weeks.

Qty Avg 8Wks =
VAR previousWeekDate =
TODAY () - WEEKDAY ( TODAY (), 1 )
RETURN
CALCULATE (
SUM ( ItemSales[Qty] ),
FILTER (
ItemSales,
ItemSales[WkStartDate] <= previousWeekDate
&& ItemSales[WkStartDate] >= ( previousWeekDate - 56 )
)
) / 8

The measure gives me the last 8 weeks average quantity from today, but I want it to give me the last 8 weeks from the end date of the slicer selection. 

Can anyone help please?

Thanks,

Michael

[v-yanjiang-msft · Accepted solution]

Hi @mboucher_rcr ,

According to your description, as in your formula below, you specify the end date is the closest Saturday before today, not the end date of the slicer selection. 

VAR previousWeekDate =
TODAY () - WEEKDAY ( TODAY (), 1 )

Modify you formula like this:

Qty Avg 8Wks =
VAR SelectedDay =
    MAXX ( ALLSELECTED ( 'Date' ), 'Date'[Date] )
VAR previousWeekDate =
    SelectedDay - WEEKDAY ( SelectedDay, 1 )
RETURN
    CALCULATE (
        SUM ( ItemSales[Qty] ),
        FILTER (
            ItemSales,
            ItemSales[WkStartDate] <= previousWeekDate
                && ItemSales[WkStartDate] >= ( previousWeekDate - 56 )
        )
    ) / 8

Get the correct result in my sample.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Ashish_Mathur]

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the WkStartDate column of the ItemSales table to the Date column of the Calendar Table.  Write this measure:

= CALCULATE(SUM(ItemSales[Qty]),DATESBETWEEN('Calendar'[Date],max('Calendar'[Date])-56,max('Calendar'[Date])))/8

Hope this helps.

[lbendlin]

Please define what you mean by "week" .  Ideally you have a calendar table with week numbers. How do you handle transitions between (fiscal) years ?  Say, "today" is February 1.  What's your definition of "past 8 weeks" in that scenario?