cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mboucher_rcr
Frequent Visitor

Last 8 Weeks Relative to Slicer End Date

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

 

1 ACCEPTED SOLUTION
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_1-1656407704906.png

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.

View solution in original post

3 REPLIES 3
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_1-1656407704906.png

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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?

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.