The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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])
Solved! Go to Solution.
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.
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
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.
User | Count |
---|---|
83 | |
83 | |
37 | |
34 | |
32 |
User | Count |
---|---|
92 | |
79 | |
61 | |
51 | |
51 |