Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Drobinson1
Helper III
Helper III

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])

 

 

1 ACCEPTED 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.

View solution in original post

2 REPLIES 2
CahabaData
Memorable Member
Memorable Member

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

www.CahabaData.com

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.