Forum Discussion

jrickerts's avatar
jrickerts
New Member
3 years ago
Solved

Filter by Slicer Selection

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. ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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