Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Weight Average

Hello,   I'm looking to get help with a formula that will get me the weighted average based off a set of paramters. I would like the formula to take the sum of spend per material number and divide ...
Need Help
  • fhill's avatar
    fhill
    5 years ago

    This the DateAdd (-1 Year) option instead of the DATE command.....   (Sorry for the change in TABLE name and columns in the version below vs. my original post.)  But you only have to change the FILTER Portion to use DATEADD vs. DATE(Y)(M)(D)

     

    1_Year_Avg_Spend = CALCULATE( SUM( 'Table'[USD Spend]) / SUM('Table'[Qty]), FILTER( 'Table', 'Table'[Document Date] > DATEADD('Table'[Document Date], -1, YEAR)))
  • fhill's avatar
    fhill
    5 years ago

    Try this....

    Last_Pur_Price = CALCULATE( AVERAGE( 'Table'[Part Price]), FILTER('Table', 'Table'[Document Date] = [Last_Pur_Date]))
     
    If you had multiple sales on the same day, assuming you want the average of the two?
     
     

     

fhill's avatar
fhill
Resident Rockstar
5 years ago

Since you will want the Last Purchase Date, and we'll use it minus 1 year in the CAL formula, start with that as it's own measure:

LastPurchaseDate = CALCULATE(LASTDATE('1 Year Trend'[Date]), FILTER(ALLEXCEPT('1 Year Trend', '1 Year Trend'[Part#]), 1=1))
** We have to include the more complicated Filter, to include this same value in the next Calculation.
 
I wasn't sure if you were looking for an Average of the 'Spend' or 'Spend divided by Qty'?
1_Year_Avg_Price = CALCULATE(AVERAGE('1 Year Trend'[Total Spend]), FILTER('1 Year Trend', '1 Year Trend'[Date] > DATE((YEAR([LastPurchaseDate])-1), MONTH([LastPurchaseDate]), DAY([LastPurchaseDate]))))
 
1_Year_Avg_Price_PerQty = CALCULATE((SUM('1 Year Trend'[Total Spend]) / SUM('1 Year Trend'[Qty])), FILTER('1 Year Trend', '1 Year Trend'[Date] > DATE((YEAR([LastPurchaseDate])-1), MONTH([LastPurchaseDate]), DAY([LastPurchaseDate]))))

 

 

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello fhill 

 

That first formula worked like a charm.

 

I'm looking for the second option you provided for the average. I'm getting a error with the formula though. Any ideas?

  • fhill's avatar
    fhill
    Resident Rockstar
    5 years ago

    This the DateAdd (-1 Year) option instead of the DATE command.....   (Sorry for the change in TABLE name and columns in the version below vs. my original post.)  But you only have to change the FILTER Portion to use DATEADD vs. DATE(Y)(M)(D)

     

    1_Year_Avg_Spend = CALCULATE( SUM( 'Table'[USD Spend]) / SUM('Table'[Qty]), FILTER( 'Table', 'Table'[Document Date] > DATEADD('Table'[Document Date], -1, YEAR)))
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      You my friend, a genius! Thank you! What a journey haha. 

       

      One last thing... is it possible to turn that first formula you did into telling me what the price was on that date?

       

      Orginal formula = 

      CALCULATE(LASTDATE(tbReceivalHistory[Document Date]),FILTER(ALLEXCEPT(tbReceivalHistory,tbReceivalHistory[Part Number (Material)]),1=1))
       
      I want this to tell me the piece price on that date it's finding.
      • fhill's avatar
        fhill
        Resident Rockstar
        5 years ago

        Try this....

        Last_Pur_Price = CALCULATE( AVERAGE( 'Table'[Part Price]), FILTER('Table', 'Table'[Document Date] = [Last_Pur_Date]))
         
        If you had multiple sales on the same day, assuming you want the average of the two?