Daily Avg Sales - Combining Two Time Periods

[Whitewater100]

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!

[Anonymous]

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.

[Whitewater100]

Hi Polly:

Please find the file. Thank you for your assistance. 

[Anonymous]

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.

[Whitewater100]

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

[Whitewater100]

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!

)

[Anonymous]

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.

[amitchandak]

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

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

[Whitewater100]

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.