topic Re: CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS in DAX Commands and Tips

CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

SelenaZ — Thu, 15 Oct 2020 04:34:15 GMT

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

Re: CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

amitchandak — Thu, 15 Oct 2020 06:12:01 GMT

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

Re: CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

SelenaZ — Thu, 15 Oct 2020 06:33:57 GMT

How can I share a file in this environment?

Re: CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

SelenaZ — Thu, 15 Oct 2020 07:07:13 GMT

@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!

Re: CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

wdx223_Daniel — Thu, 15 Oct 2020 08:53:24 GMT

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]

Re: CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

SelenaZ — Thu, 15 Oct 2020 09:48:45 GMT

wdx223_Daniel thank you so much!

Re: CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

wdx223_Daniel — Fri, 16 Oct 2020 00:26:12 GMT

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

Re: CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

SelenaZ — Fri, 16 Oct 2020 00:48:59 GMT

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

Re: CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

wdx223_Daniel — Fri, 16 Oct 2020 01:43:20 GMT

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

Re: CALCULATING PROMO SALES IN DIFFERENT STORES OVER UNIQUE PERIODS

SelenaZ — Fri, 16 Oct 2020 02:26:17 GMT

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