topic Re: Average daily sales with filter out dates in DAX Commands and Tips

Average daily sales with filter out dates

Anonymous — Fri, 30 Sep 2022 16:16:21 GMT

I'm measuring promotional effectiveness and want to find the average daily amount of $ made of certain products when they're not on promotion.

I've got the following measure for calculating average $ made each week:

AVERAGEX(

VALUES('DimDate'[Day]),

[Sales Total ($)])

What I need is a filter that excludes any sales made, of the products included in the promotion, and their respective dates.

I have this measure with filter for calculating all $ made on promotion products during their respective dates:

CALCULATE (

SUM ( Sales[Sales_Value] ),

FILTER (

DimDate,

DimDate[Date] >= MIN( DimPromotion[StartDate] )

&& DimDate[Date] <= MAX ( DimPromotion[EndDate] )))

I've tried changing the format to exclude those dates instead but it's results in blank values.

Thank you for any guidance.

Re: Average daily sales with filter out dates

tamerj1 — Fri, 30 Sep 2022 17:37:57 GMT

Hi Anonymous

what is the relationship between Sales and DimPromotion?

Re: Average daily sales with filter out dates

Anonymous — Fri, 30 Sep 2022 18:40:44 GMT

Hi tamerj1

the relationship is many to one. I created an index for each row in Promotions and joined it to ProductID in my Sales table.

Re: Average daily sales with filter out dates

tamerj1 — Fri, 30 Sep 2022 18:47:34 GMT

Anonymous
Sorry but can I ask which columns are you using in your table/matrix?

Re: Average daily sales with filter out dates

Anonymous — Fri, 30 Sep 2022 19:03:00 GMT

My table has these columns so far:

1. Sales[ProductID]
2. Product'ProductName]

3. Total amount $ made during promotion for respective product (measure)

Product table is joined to Sales through ProductID, just like Promotion.

I'd like for column 4 and 5 to represent average daily amount $ made for each product, during promotion and outside the promotion.

I'm new tp power bi so apologies for any ambiguity!

Re: Average daily sales with filter out dates

tamerj1 — Sat, 01 Oct 2022 03:08:06 GMT

Good Moring Anonymous
Sorry for the late response, it was too late last night. Let's start trying the following

None Prpotion Sales = VAR _StartDate = CALCULATE ( MAX ( DimPromotion[StartDate] ), CROSSFILTER ( Sales[Index], DimPromotion[Index], BOTH ) ) VAR _EndDate = CALCULATE ( MAX ( DimPromotion[EndDate] ), CROSSFILTER ( Sales[Index], DimPromotion[Index], BOTH ) ) RETURN CALCULATE ( SUM ( Sales[Sales_Value] ), DimDate[Date] < _StartDate && DimDate[Date] > _EndDate )

Re: Average daily sales with filter out dates

Anonymous — Sat, 01 Oct 2022 07:18:36 GMT

Hi tamerj1

Thanks for your reply!

This gave blank values unfortunately. Do you know what could possibly cause this?

Re: Average daily sales with filter out dates

tamerj1 — Sat, 01 Oct 2022 07:43:00 GMT

Hi Anonymous
Would you like to connect perhaps via teams to zoom?

Re: Average daily sales with filter out dates

Anonymous — Tue, 04 Oct 2022 06:28:25 GMT

Apologies for the long delay in replying.

Thank you very much for offering your help but in the end I used a bridge table to get around this issue and now I can get it to work.