Forum Discussion

Amerivike's avatar
Amerivike
Advocate II
6 years ago
Solved

Average 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/202...
  • fhill's avatar
    fhill
    6 years ago

    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 ) + 1
     
    This 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 take
     
    Measure = 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

Amerivike's avatar
Amerivike
Advocate II
6 years ago

Hey 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's avatar
Amerivike
Advocate II
6 years ago

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!