Forum Discussion

Drobinson1's avatar
Drobinson1
Helper III
10 years ago
Solved

Calculate between dates

I am trying to calculate total usage based on a timeline slicer that uses invoice date from a sales table.

 

The sales table and recipe table are unrelated and cannot be related due to multiple values per each column.

 

 

I cannot figure out how to haev teh calculated column take into account the timelin slicer from teh invoice table.

 

Total Usage= var currentProduct='RC_FULL_LEVEL_RECIPES'[Part_0] Var currentwarehouse=RC_FULL_LEVEL_RECIPES[Warehouse_0] Var MinDate=CALCULATE(MIN(RC_Daily_Sales_Report_Invoice_Line_Detail[Invoice_Date])) Var Maxdate=CALCULATE(MAX(RC_Daily_Sales_Report_Invoice_Line_Detail[Invoice_Date])) Return IF(RC_FULL_LEVEL_RECIPES[RawQtyUsed]=1,CALCULATE(SUM(RC_Daily_Sales_Report_Invoice_Line_Detail[Invoice_Qty]),FILTER(ALL(RC_Daily_Sales_Report_Invoice_Line_Detail[Warehouse]),RC_Daily_Sales_Report_Invoice_Line_Detail[Warehouse]=currentwarehouse),FILTER(ALL(RC_Daily_Sales_Report_Invoice_Line_Detail[PART_CODE]),RC_Daily_Sales_Report_Invoice_Line_Detail[PART_CODE]=currentProduct))*RC_FULL_LEVEL_RECIPES[RawQtyUsed],CALCULATE(SUM(RC_Daily_Sales_Report_Invoice_Line_Detail[Weight]),FILTER(ALL(RC_Daily_Sales_Report_Invoice_Line_Detail[Warehouse]),RC_Daily_Sales_Report_Invoice_Line_Detail[Warehouse]=currentwarehouse),FILTER(ALL(RC_Daily_Sales_Report_Invoice_Line_Detail[PART_CODE]),RC_Daily_Sales_Report_Invoice_Line_Detail[PART_CODE]=currentProduct),FILTER(ALL(RC_Daily_Sales_Report_Invoice_Line_Detail[Invoice_Date]),RC_Daily_Sales_Report_Invoice_Line_Detail[Invoice_Date]>=MinDate),FILTER(ALL(RC_Daily_Sales_Report_Invoice_Line_Detail[Invoice_Date]),RC_Daily_Sales_Report_Invoice_Line_Detail[Invoice_Date]<=Maxdate))*RC_FULL_LEVEL_RECIPES[RawQtyUsed])

 

 

Need Help
  • Drobinson1's avatar
    Drobinson1
    10 years ago

    I think I solve my issue on this one.  Date slicers can't be used for a calculated column.  On to a new problem having to do with weighted averages.  I will post in a seperate thread.

2 Replies

  • CahabaData's avatar
    CahabaData
    Memorable Member
    10 years ago

    it might be more helpful if you post a short sample of the data itself with column names

     

    with a statement of what calculation you seek

    • Drobinson1's avatar
      Drobinson1
      Helper III
      10 years ago

      I think I solve my issue on this one.  Date slicers can't be used for a calculated column.  On to a new problem having to do with weighted averages.  I will post in a seperate thread.