topic Re: Daily Avg Sales - Combining Two Time Periods in DAX Commands and Tips

Daily Avg Sales - Combining Two Time Periods

Whitewater100 — Wed, 26 Jan 2022 18:15:31 GMT

Hello:

I'm seeking input on how to calculate (efficiently) a daily average for sales for two weeks (can be a month) before and two weeks after a promotional period. The promotional period example is in a separate table called Ad_Campaigns. Here is a shot of the info:

Campaign	Start	End	Marketing Spend	Brand	Year	Discount%
Spring-Easter -5% Off	3/29/2020	4/11/2020	$ 660	LookSharp	2020	0.05
Spring-Easter -5% Off	3/29/2020	4/11/2020	$ 396	SmoothDrive	2020	0.05
Spring-Easter -5% Off	3/29/2020	4/11/2020	$ 396	Stealth	2020	0.05
Spring-Easter -5% Off	3/29/2020	4/11/2020	$ 1,848	ZEGO	2020	0.05

I'm able to figure out how to calculate sales during this period but combining two periods to get an average is my question.

I have a date table and sales table. I'll paste below. Thanks much!

Re: Daily Avg Sales - Combining Two Time Periods

amitchandak — Wed, 26 Jan 2022 18:47:33 GMT

Whitewater100 , if you already have a sales measure for the period. you can try a measure

averaged(values('Date'[Date]), [sales period])

Re: Daily Avg Sales - Combining Two Time Periods

Whitewater100 — Wed, 26 Jan 2022 19:36:57 GMT

Thank you for replying. I have the sales periods (Pre & Post) promotion. e.g,

Spring '21 Pre-Ad AUPD = CALCULATE(

AVERAGEX(Dates,

[Units]),

DATESBETWEEN(Dates[Date],

Date(2021, 3, 7), Date(2021, 3, 20)

FILTER(Products,

Products[Brand] IN {"LookSharp", "SmoothDrive", "Stealth", "ZEGO"}

)

and

Spring '21 Pre-Ad AUPD = CALCULATE(

AVERAGEX(Dates,

[Units]),

DATESBETWEEN(Dates[Date],

Date(2021, 3, 7), Date(2021, 3, 20)

FILTER(Products,

Products[Brand] IN {"LookSharp", "SmoothDrive", "Stealth", "ZEGO"}

)

But my question is how to combine the pre and post into one measure.

Thank you.

Re: Daily Avg Sales - Combining Two Time Periods

Anonymous — Tue, 01 Feb 2022 03:48:14 GMT

Hi Whitewater100 ,

I have created a simple sample. Please refer to it to see if it helps you.

Measure = var national_= IF(SELECTEDVALUE('Table'[date])>=DATE(2021,10,1)&&SELECTEDVALUE('Table'[date])<=DATE(2021,10,7),"National day",BLANK()) var result = CALCULATE(AVERAGE('Table'[Marketing]),FILTER(ALL('Table'),ISBLANK(national_))) var holiday_average= CALCULATE(AVERAGE('Table'[Marketing]),FILTER(ALL('Table'),'Table'[date]>=DATE(2021,10,1)&&'Table'[date]<=DATE(2021,10,7))) return IF(SELECTEDVALUE('Table'[date])>=DATE(2021,10,1)&&SELECTEDVALUE('Table'[date])<=DATE(2021,10,7),holiday_average,result)

If I have misunderstand your meaning, pleasae provide your pbix without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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

Re: Daily Avg Sales - Combining Two Time Periods

Whitewater100 — Wed, 02 Feb 2022 17:46:38 GMT

Hello and thank you:

For example the Fall 2021 Ad is from 10-30-21 to 11-19-21.

I take a measure for two weeks before and two weeks after.

Fall'21 Pre-Ad AUPD = CALCULATE(

AVERAGEX(Dates,

[Units]),

DATESBETWEEN(Dates[Date],

Date(2021, 10, 16), Date(2021, 10, 29)

)

Then 2 Weeks after

Fall'21 Post-AD AUPD = CALCULATE(

AVERAGEX(Dates,

[Units]),

DATESBETWEEN(Dates[Date],

Date(2021, 11, 20), Date(2021, 12, 4)

)

Then I average them. This is my question how to do these three steps more efficiently:

Fall '21 Base = ([Fall'21 Post-AD AUPD] + [Fall'21 Pre-Ad AUPD])/2

Ideally, the number of days to analyze both before and after the ad is equal to the number of the days the ad ran. But for now I was seeking a better way to come up with the average combining the time periods of before and after.

Thanks to both of you for replying!

)

Re: Daily Avg Sales - Combining Two Time Periods

Anonymous — Thu, 03 Feb 2022 01:23:13 GMT

Hi Whitewater100 ,

If your formula works well, please have a try.

Fall '21 Base= var Fall'21 Pre-Ad AUPD = CALCULATE ( AVERAGEX ( Dates, [Units] ), DATESBETWEEN ( Dates[Date], DATE ( 2021, 10, 16 ), DATE ( 2021, 10, 29 ) ) ) var Fall'21 Post-AD AUPD = CALCULATE ( AVERAGEX ( Dates, [Units] ), DATESBETWEEN ( Dates[Date], DATE ( 2021, 11, 20 ), DATE ( 2021, 12, 4 ) ) ) return (Fall'21 Post-AD AUPD- Fall'21 Pre-Ad AUPD)/2

If I have misunderstood your meaning, please provide your pbix without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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

Re: Daily Avg Sales - Combining Two Time Periods

Whitewater100 — Thu, 03 Feb 2022 01:59:08 GMT

Hello:

I had planned to attach my file, however, I do not see where to attach my file here. Can you tell me the best way to send?

Thank you

Re: Daily Avg Sales - Combining Two Time Periods

Anonymous — Thu, 03 Feb 2022 05:08:16 GMT

Hi Whitewater100 ,

you can drop your file to attach.

Best Regards

Community Support Team _ Polly

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

Re: Daily Avg Sales - Combining Two Time Periods

Whitewater100 — Thu, 03 Feb 2022 14:07:39 GMT

Hi Polly:

Please find the file. Thank you for your assistance.