Filter by Slicer Selection

[jrickerts]

Good day

I want to see how effective a promotion was by comparing rate of sale per day off promotion before a promotion started and compare it with the rate of sale per day during the promotion. So if a product sold 10 units a day off promotion 7 days before I put that product on promotion, I am hoping to see that I see more than 10 units a day during the promotion. 

I cannot seem to isolate the products that are on a particular promotion. 

In the above screenshot, I selected two promotion, called December 2022 Month End Promotion and Hyper Weekend Promotion. Firstly, I must be able to determine what the MIN Start Date of the two promotion are. In this example, I hard coded the date. The minimum start date for the two selected promotions is 15 December 2022. Then I want to see how many products were sold 7 days before the 15th of December 2022. In the table, it returns all 5 Products in the product table. I cancelled out product 4, because that product wasn't sold on either of the selected promotions. 

There is also no result (blank) for product 2. I want this result to be zero. And finally the total should be correct at the bottom. It doesn't show a result but I'd like it to be 409. 

I tried to create a new table showing the promotional products, but I have had little success in filtering the table to only show the products that are on the selected promotions. 

Here is my model. 

Here is my DAX measure. 

Off Promo Qty Sold Pre-Promo =

VAR selectedproduct = SELECTEDVALUE(Products[ProductID])

VAR maxdate = CONVERT("2022-12-15", DATETIME)

VAR mindate = maxdate - 7

RETURN

CALCULATE(SUM(Sales[Qty Sold]), ALL(Sales), Sales[PromotionID] = BLANK(), 'Sales'[Date] < maxdate, Sales[Date] >= mindate, 'Products'[ProductID] = selectedproduct)

Can anyone assist? 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[v-shex-msft · Accepted solution]

Hi @jrickerts,

You can try to use the following measure formula if it suitable for your requirement:

Off Promo Qty Sold Pre-Promo =
VAR selectedproduct =
    VALUES ( Products[ProductID] )
VAR currPromotion =
    VALUES ( Promotion[PromotionID] )
RETURN
    CALCULATE (
        SUM ( Sales[Qty Sold] ),
        FILTER (
            ALL ( Sales ),
            'Sales'[ProductID]
                IN selectedproduct
                    && COUNTROWS (
                        FILTER (
                            ALLSELECTED ( Promotion ),
                            [PromotionID]
                                IN currPromotion
                                && 'Sales'[Date] < Promotion[EndDate]
                                && Sales[Date] >= Promotion[StartDate]
                        )
                    ) > 0
        )
    )

Regards,

Xiaoxin Sheng