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

fhill's avatar
fhill
Resident Rockstar
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!