CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

[SelenaZ]

Hi everyone,

I have the following challenge.

One table contains sales in different stores (fields StoreID, SalesDate, ProductID, SalesQty)

The other table containes only the stores selected for promotions (fields StoreID, PromoStart, PromoEnd).

How do I calculate the sum of all products sold on Promo in all thes stores, given that the period of Promo can be unique for every store? In other words, I need a measure showing the total promo sales of all stores in a specific period of time (given by Pivot Filter), knowing that some stores were selling Promo only for a partial period, and some did not sell at all.

Currently I use INTERSECT  to look only at stores that had promos, but I cannot factor in the unique period for each store.

Could you please help?

Many thanks!

SelenaZ

[wdx223_Daniel · Accepted solution]

first to create a date table and mark it as date table, then create a relationship bewteen dates[date] and sales[date]

and try these code

Promot_QTY :=
SUMX (
    Promot,
    VAR store = Promot[Store ID]
    RETURN
        CALCULATE (
            SUM ( Sales[Qty] ),
            KEEPFILTERS ( DATESBETWEEN ( Dates[Date], Promot[StartDate], Promot[EndDate] ) ),
            Sales[StoreID] = store
        )
)

unPromot_QTY:=SUM(Sales[Qty])-[Promot_QTY]

[amitchandak]

@SelenaZ , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[SelenaZ]

@amitchandak Here you go, thanks!

Table promotions

Store ID	Promo Name	StartDate	EndDate
CT0101	Event01	01/01/2020	01/02/2020
CT0102	Event01	15/01/2020	15/02/2020
CT0103	Event01	01/02/2020	01/03/2020

Table Sales

StoreID	Date	ProductID	Qty
CT0101	02/01/2020	Product 1	10
CT0101	02/01/2020	Product 2	10
CT0101	02/02/2020	Product 1	10
CT0101	02/02/2020	Product 2	10
CT0102	14/01/2020	Product 1	10
CT0102	20/01/2020	Product 1	10
CT0102	20/01/2020	Product 1	10
CT0102	16/02/2020	Product 1	10
CT0103	15/01/2020	Product 1	10
CT0103	20/01/2020	Product 2	10
CT0103	13/02/2020	Product 2	10
CT0103	20/02/2020	Product 2	10
CT0103	01/03/2020	Product 2	5

Sample output

Month	Promo	Non-Promo
January	40	30
February	20	30
March	5	0

Thank you for your help!

[wdx223_Daniel · Accepted solution]

first to create a date table and mark it as date table, then create a relationship bewteen dates[date] and sales[date]

and try these code

Promot_QTY :=
SUMX (
    Promot,
    VAR store = Promot[Store ID]
    RETURN
        CALCULATE (
            SUM ( Sales[Qty] ),
            KEEPFILTERS ( DATESBETWEEN ( Dates[Date], Promot[StartDate], Promot[EndDate] ) ),
            Sales[StoreID] = store
        )
)

unPromot_QTY:=SUM(Sales[Qty])-[Promot_QTY]

[SelenaZ]

@wdx223_Daniel thank you so much!

One more question: how can I be able to break down sales by month (Month is in the Date Table)

[wdx223_Daniel]

are you mean putting the month field on the row of matrix?

[SelenaZ]

Well, when I try to break down the results (promo and non-promo sales) by a time period, I can get sales by month but not by week, although my calendar table does include week numbers as well. Could it be an innate DAX limitation?

Thank you1

[wdx223_Daniel · Accepted solution]

you can custom your date table, using date functions to add week column

[SelenaZ]

@wdx223_Daniel Initially breaking dow by week did not work, but now (after restart) it does. Thank you for your help! Cheers!

[SelenaZ]

How can I share a file in this environment?