Forum Discussion
Advocate IIAverage Sales Per Account Per Week With TImeline
My data set looks like this except I have all states, 60,000 accounts and dozens of products:
| State | Account | Date | Product | Volume |
| Texas | Acct A | 1/1/2020 | Prod 1 | 6 |
| Texas | Acct A | 1/7/2020 | Prod 2 | 5 |
| Texas | Acct A | 1/7/2020 | Prod 1 | 4 |
| Texas | Acct A | 1/14/2020 | Prod 1 | 8 |
| Texas | Acct A | 1/14/2020 | Prod 3 | 8 |
| Texas | Acct A | 1/14/2020 | Prod 2 | 9 |
| Texas | Acct A | 1/21/2020 | Prod 3 | 12 |
| Texas | Acct A | 1/21/2020 | Prod 2 | 16 |
| Texas | Acct A | 1/28/2020 | Prod 1 | 14 |
| Texas | Acct B | 1/1/2020 | Prod 2 | 15 |
| Texas | Acct B | 1/1/2020 | Prod 1 | 12 |
| Texas | Acct B | 1/7/2020 | Prod 3 | 6 |
| Texas | Acct B | 1/14/2020 | Prod 3 | 11 |
| Texas | Acct B | 1/14/2020 | Prod 1 | 3 |
| Texas | Acct B | 1/21/2020 | Prod 3 | 15 |
| Texas | Acct B | 1/21/2020 | Prod 2 | 8 |
| Texas | Acct B | 1/21/2020 | Prod 1 | 6 |
| Texas | Acct B | 1/20/2020 | Prod 2 | 4 |
| Texas | Acct B | 1/28/2020 | Prod 1 | 3 |
The output I am looking for is the average volume per week for a product in a state with a timeline slicer. The basic math is:
volume/accounts/total weeks.
I can calculate the volume and number of stores just fine. The difficulty is that only the weeks after the product is shipped to an account counts. If I were looking for this information for product 3, Account A would be 20 (volume)/3 (weeks) and Account B would be 32/4 (got the product 1 week before account A) so Texas would be 52 (volume)/7 (weeks)/2 (accounts). All weeks after the first week would count.
I tried using a summarize function to calculate the weeks with datediff and it works, but I loose the functionality of the timeline slicer that way:
Any help is appreciated!
I think I'm getting close, but I'm not clear on when To or Not To include Accounts in the Math? I think the 'KEY' to fixing this is to create a NEW Measure of 'Weeks' ahead of time that looks at the MIN and MAX Dates (whether by Slicer or by Product / Account if in a Final Table / View?
Weeks = WEEKNUM( MAX( 'Table'[Date] ), 2) - WEEKNUM( MIN ( 'Table'[Date] ), 2 ) + 1This will help to 'limit' the Weeks by Product (or Whatever Slicer you use?) Then you can use the 'Weeks' Measure in your Final calcuation to takeMeasure = CALCULATE( SUM( 'Table'[Volume]) / [Weeks] / DISTINCTCOUNT('Table'[Product]))Let me know if this is on the right path, and what else you need to finish off the code?
Thank You,
Forrest
5 Replies
- fhill
Resident Rockstar
I think I'm getting close, but I'm not clear on when To or Not To include Accounts in the Math? I think the 'KEY' to fixing this is to create a NEW Measure of 'Weeks' ahead of time that looks at the MIN and MAX Dates (whether by Slicer or by Product / Account if in a Final Table / View?
Weeks = WEEKNUM( MAX( 'Table'[Date] ), 2) - WEEKNUM( MIN ( 'Table'[Date] ), 2 ) + 1This will help to 'limit' the Weeks by Product (or Whatever Slicer you use?) Then you can use the 'Weeks' Measure in your Final calcuation to takeMeasure = CALCULATE( SUM( 'Table'[Volume]) / [Weeks] / DISTINCTCOUNT('Table'[Product]))Let me know if this is on the right path, and what else you need to finish off the code?
Thank You,
Forrest
AmerivikeHey Forest...this is super close. There is one problem. It is only counting the number of weeks that the account actually recieved product. I need the average to be from the first time they recieved the product to the Max selected date whether they received the product or not. Does that make sense?
- Amerivike
Actually that only can be the case when I get super granular in the data, which is not what this is meant to be as these measures will stay at the state or a higher level so this will work perfectly! You are awesome Forrest!
- Greg_Deckler
Community Champion
Amerivike - I'm not 100% clear on what the expected output from your sample data would be. Can you share what you are going for?
