I have three tables. DateTable, Store-List and Store-Transactions. Date have dates. Store-List have list of all the stores. And Store-Transactions have all the transactions happened in all the stores by date. Now I have two tasks.
- Transaction-Count - Calculate total transactions in a given date range. I think I did that correctly.
- APSD – Average transaction Per Store Per day in a given date range.
Formula for 2 is.
APSD = Transaction-Count/([Total-Days]*[ Unique-Stores-Count])
If we select Date range in PBI from 04/01/2019 – 04/30/2019 we get
Transaction-Count as 93. Which is correct.
APSD as 0.62. It calculates total distinct stores (5 Stores in a date range) and based on that it calculates 93/(30*5) = 0.62.
BUT above calculated APSD is not what I want, Here is why -
Based on Store-List table we can notice that -
- From April-1 to April-19, 2019 - Only Three Stores were open.
- From April-20 to April-27, 2019 - Only two Stores were open
- From April-28 to April-30, 2019 - Four Stores were open.
So, I want APSD formula to be modified in such a way that it calculates in this way –
APSD = 93/(19*3+8*2+3*4) = 1.09
In conclusion - Two considerations - Formula should adjust for number of days a store was not open in the selected date range. And it must count all the open stores even if does not appear in Store-Transactions table rows (which might happen if a store was open but did not do any transactions in the date range selected).
I am sharing the PBI and data files-
https://drive.google.com/file/d/16gwp8k-nni-CS8pxPU5UIsZI5W8wMhq1/view?usp=sharing
Thanks!
