To calculate average of last 2 days sales dynamically based on store_id and product

[pry76092]

Hi All,

I'm new to power BI and facing an issue while calculating sales for last 2 days. So, basically I need to find average sale for last 2 days based on different product sold in different store. I hope I have explained my question in a proper way in the below table.

So, for me I need a dax query which can create a column which can calculate the sale for each product based on store they have been sold.

Thank you for your help!

storeid	product no	date	sale	Avg sale for last 2 days of each product for each store_id)
21	42A5	11-05-2021	1	0.5
22	42A5	11-05-2021	1	0.5
21	42A5	12-05-2021	0	0.5
23	42A5	11-05-2021	0	0
23	42A6	11-05-2021	1	1
23	42A6	12-05-2021	1	1

[Whitewater100 · Accepted solution]

Hi:

I have created a model file ( using a udemy data set) that shows how to get the 2-day average by store number.

There are two calulated columns in the Date Table (Sales and an Index) and then a couple different measures in the measures folder. I wanted to test on a bigger data set. There is a separate folder for measures used.

If your model has a date table, it will be fairly easy to replicate this. I hope this works for you.

https://drive.google.com/file/d/1qr_6hDCaLL3K1jivRJg9zihCCtt6tA1X/view?usp=sharing 

[pry76092]

HI

This solution is really helpful but just has small doubt here, I don't have sales_index in my dataset so is it possible to take some other value while calculating the last 2 days' sales?

Thank You

[Whitewater100]

Hi:

You an just take the Sales for the last two days and divide by 2.  That would solve it too, so I put that 2-day sales measure in there.  Avg 2 Day Sales = [Sales Last Two Days] / 2. 

If you have a date table and go to transform, you can add  an index column starting from 1, if you were wondering. I hope you can take this as a solution. Thanks

[pry76092]

HI,

Thanks a lot for your solution. It worked for me.

thank you

[Whitewater100]

Thanks a lot!

[pry76092]

Hi,

The calculation provided by you really worked but it got stuck at one place. If there is no sale 

on a particular day ie if the sale_txns is 0 then its showing zero for last 6 days sales also.

Can you please help me in knowing what I'm doing wrong here?

date	store_id	product_id	sales	last 6 days sale	Avg last 6   days
04/03/2021	25	113654	1	1	0.17
05/03/2021	25	113654	1	2	0.33
18/03/2021	25	113654	1	1	0.17
23/03/2021	25	113654	1	2	0.33
24/03/2021	25	113654	1	2	0.33
25/03/2021	25	113654	1	3	0.50
26/03/2021	25	113654	1	4	0.67
29/03/2021	25	113654	1	4	0.67
30/03/2021	25	113654	1	4	0.67
31/03/2021	25	113654	1	4	0.67
01/04/2021	25	113654	1	4	0.67
02/04/2021	25	113654	0	0	0

[Whitewater100]

Hi:

Can you add this minor change to check that there are sales:

Sales Last 2 Days =

var currdate = MAX(Dates[Date])

var dteinx = SELECTEDVALUE(Dates[Sales Index])

return

If(dteinx > 1 && NOT(ISBLANK([Total Sales])),

CALCULATE([Total Sales], DATESINPERIOD(Dates[Date], currdate,-2,DAY)))

[pry76092]

HI,

Thanks for your response but still its not working.

What I did over here is, I calculated Total Sales =sum(txn[sales])

and after that 

Sales Last 2 Days =

var currdate = MAX(Dates[Date])

var dteinx = SELECTEDVALUE(Dates[Sales Index])

return

If(dteinx > 1 && NOT(ISBLANK([Total Sales])),

CALCULATE([Total Sales], DATESINPERIOD(Dates[Date], currdate,-2,DAY)))

[pry76092]

Just to be more elaborate,

I have a txn_store table which contains column like date, transaction count or sales(which is 1 for transaction and 0 for no transaction), Index.

So, I calculated Total sales =sum(txn_store[sales])

Sales Last 2 Days =

var currdate = MAX(txn_store[Date])

var dteinx = SELECTEDVALUE(txn_store[Sales Index])

return

If(dteinx > 1 && NOT(ISBLANK([Total Sales])),

CALCULATE([Total Sales], DATESINPERIOD(txn_store[Date], currdate,-2,DAY))).

[Whitewater100]

HI:

Are you OK now?

If it is not working like you want, can I see sample data and the table relationships?

It sounds like you apllied the update to your scenario:-) - just checking..

Thanks..

[pry76092]

HI,

I'm good thanks for asking.

I cant share the data but I have attached the relationship diagram here. I hope this will help.

[Whitewater100]

Hi:

I wanted to ask if you could add a date table to your model,connecting to the txn_store table where Dates in the one side and your fact table is the many side. I will provide a calendar to get you started. You can go to Get Data and bring in this excel file. It can be added to,too.

https://docs.google.com/spreadsheets/d/1zu1X16A0d-bvR3yWSm-Fo4MdI8dEUtPz/edit?usp=sharing&ouid=10785... 

Once it is in your model, under tabletools you can choose the Date field and mark it as a Date Table. The table name is Dates. This will allow the use of time intl calculations in your model.

Is it possible to make the cust_store table on the one side as well? Depending what's in your fact table (txn_store) if you have both a store id and a separate customer ID, then you would have two separate dimension tables that only have unique values, connected as one to many with txn_stores.

If you can do this great, as your model will be better. Once your Date Table is in it will be easier to address any issues leftover.

I hope this is helpful.

Thanks..

[Whitewater100]

Hi:

Please try  to have a date table and connect it to your date field in your main fact table pictured above. *Mark as Date Table. You can go to modeling, new table and name it DATES. Calendar auto will make a very basic one for you. New Table.  DATES = CALENDARAUTO()...

For you basic sales measure, appearing when placed on your canvas

Sales =SUM( YourTableName[Sales])  this will give you all sales. Now this measure is part of the next measure.

This next measure(L2DaysSales) relies on your first measure and is now helped by your new date table.

Sales Last 2 Days = CALCULATE([Sales], DATESBETWEEN(Dates[Date]), 
                               MAX(Dates[Date]) -2, MAX(Dates[Date]))

Avg 2 Day = AVERAGEX( DATESINPERIOD(Dates[Dates, LASTDATE(Dates[Date])
, -2,DAY), [Sales Last 2 Days])  or divide above by 2.

I hope this helps. You can put these in a table or matrix visual, using Date field from Date table and then the measures you want to see.

Hope this helps.
Avg L 2 Days

[pry76092]

Thanks for your response. But It's not working for me, since I need to calculate the sales of products based on the store. There are chances when the same product is being sold in two different stores. So, I want to calculate the sales of a store given a specific product.