Calculate same day of week sales for the previous 2 weeks

[appleceo]

Hi,

I am trying to use DAX to calculate the average weekly sales for the deal products in the same days of the week in the two weeks before the deal dates.

For example, for the data below, product ABC is on deal 2 which starts in 1/5 (Sunday) and end in 1/6 (Monday). I want to calculate the average Sunday+Monday sales for 12/22,12/23,12/29,12/30 for product ABC which is  (78+442+222+100)/2 WEEKS=421. Similar for deal 1.

Deal Name	Product	Sales	Deal Start Date	Deal End date	Date
	ABC	100			12/30/2019
	ABC	50			12/31/2019
Deal 1	ABC	1	1/1/2020	1/1/2020	1/1/2020
	ABC	2			1/2/2020
	ABC	1			1/3/2020
	ABC	3	1/1/2020	1/1/2020	1/4/2020
Deal 2	ABC	4	1/5/2020	1/6/2020	1/5/2020
Deal 2	ABC	4	1/5/2020	1/6/2020	1/6/2020
	ABC	25			12/25/2019
	ABC	233			12/26/2019
	ABC	112			12/27/2019
	ABC	343			12/28/2019
	ABC	222			12/29/2019
	bcd	12			12/25/2019
	bcd	16			12/26/2019
	bcd	20			12/27/2019
	bcd	24			12/28/2019
	bcd	28			12/29/2019
	bcd	32			12/30/2019
	bcd	36			12/31/2019
	ABC	22			12/20/2019
	ABC	46			12/21/2019
	ABC	78			12/22/2019
	ABC	442			12/23/2019
	ABC	34			12/24/2019
	ABC	22			12/15/2019
	ABC	456			12/16/2019
	ABC	23			12/17/2019
	ABC	1			12/18/2019
	ABC	2			12/19/2019

Thank you for your help!

[v-henryk-mstf · Accepted solution]

Hi @appleceo ,

According to your needs, I have done the following tests for reference:

M = 
VAR _dealsdate =
    CALCULATE (
        MAX ( 'Table'[Deal Start Date] ),
        FILTER ( 'Table', 'Table'[Deal Name] = MAX ( 'Table'[Deal Name] ) )
    )
VAR _dealedate =
    CALCULATE (
        MAX ( 'Table'[Deal End date] ),
        FILTER ( 'Table', 'Table'[Deal Name] = MAX ( 'Table'[Deal Name] ) )
    )
VAR _pre1wdsate =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _dealsdate - 7 )
    )
VAR _pre1wdeate =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _dealedate - 7 )
    )
VAR _pre2wsdate =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _dealsdate - 14 )
    )
VAR _pre2wedate =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = _dealedate - 14 )
    )
RETURN
    IF (
         ( MAX ( 'Table'[Deal Name] ) ) = "",
        BLANK (),
        DIVIDE ( _pre1wdsate + _pre1wdeate + _pre2wsdate + _pre2wedate, 2, 0 )
    )

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and test data. Let me know immediately, looking forward to your reply.

Best Regards,
Henry

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

[amitchandak]

@appleceo , Same week day is 7 day behind. With help from Dat table

example

7 behind Sales = CALCULATE(SUM(Sales[Sales]),dateadd('Date'[Date],-7,DAy))

Also refer my WOW week blog

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.