Average daily sales with filter out dates

[Anonymous]

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.

[tamerj1]

Hi @Anonymous 

what is the relationship between Sales and DimPromotion?

[Anonymous]

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.

[tamerj1]

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

[Anonymous]

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!

[tamerj1]

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
    )

[Anonymous]

Hi @tamerj1 

Thanks for your reply!

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

[tamerj1]

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

[Anonymous · Accepted solution]

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.