topic Re: To calculate average of last 2 days sales dynamically based on store_id and product in DAX Commands and Tips

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

pry76092 — Thu, 31 Mar 2022 18:00:34 GMT

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

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

Whitewater100 — Thu, 31 Mar 2022 18:02:08 GMT

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

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

pry76092 — Mon, 04 Apr 2022 09:55:47 GMT

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.

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

Whitewater100 — Mon, 04 Apr 2022 14:08:29 GMT

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

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

pry76092 — Mon, 04 Apr 2022 18:48:43 GMT

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

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

Whitewater100 — Mon, 04 Apr 2022 18:57:05 GMT

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

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

pry76092 — Mon, 04 Apr 2022 19:31:58 GMT

HI,

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

thank you

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

Whitewater100 — Mon, 04 Apr 2022 19:50:51 GMT

Thanks a lot!

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

pry76092 — Tue, 05 Apr 2022 12:55:05 GMT

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

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

Whitewater100 — Tue, 05 Apr 2022 14:18:47 GMT

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)))

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

pry76092 — Tue, 05 Apr 2022 14:28:38 GMT

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)))

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

pry76092 — Tue, 05 Apr 2022 14:45:54 GMT

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))).

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

Whitewater100 — Tue, 05 Apr 2022 14:57:47 GMT

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..

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

pry76092 — Tue, 05 Apr 2022 15:52:47 GMT

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.

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

Whitewater100 — Tue, 05 Apr 2022 17:02:17 GMT

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=107858444427060984721&rtpof=true&sd=true

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..