Forum Discussion
Sum Value Over Relative Date Range
Hello,
I have a table like this:
| WorkOrder | Whse | ComponentItemNumber | QtyIssued | QtyCommitted | WODueDate_CC | OrderStatus |
| 0083322 | 000 | 00-201616 | 30 | 0 | 1/5/2023 | C |
| HZ152W1 | 000 | 00-201616 | 1 | 0 | 1/16/2023 | C |
| HY371-2 | 000 | 00-201616 | 3 | 0 | 1/20/2023 | C |
| HX400-1 | 000 | 00-201616 | 0 | 10 | 1/30/2023 | F |
| HX943-1 | 000 | 00-201616 | 2 | 0 | 2/9/2023 | C |
| HY201-2 | 000 | 00-201616 | 0 | 1 | 2/15/2023 | F |
| HY732W1 | 000 | 00-201616 | 0 | 1 | 2/16/2023 | F |
| HY582-2 | 000 | 00-201616 | 0 | 2 | 3/10/2023 | F |
| HW992-1 | 000 | 00-201616 | 0 | 2 | 4/14/2023 | F |
| HZ948-1 | 000 | 00-201616 | 0 | 2 | 5/2/2023 | F |
| HX675-1 | 000 | 00-201616 | 0 | 1 | 5/17/2023 | F |
| HX678-9 | 000 | 00-201616 | 0 | 1 | 8/17/2023 | F |
| HX672-3 | 000 | 00-201616 | 0 | 1 | 10/19/2023 | F |
| HX677-7 | 000 | 00-201616 | 0 | 1 | 11/11/2023 | F |
I need to sum the QTYCommited where the Orderstatus = "F" & the WODueDate is within the last two months, or within the next 3 months. How would this be written?
I came up with this -
but I'm getting the error "A function DATEADD has been used in a true/false expression that is used as a table filter expression. This is not allowed."
What other dax function should I be using for this? Am I missing something?
Thanks
Hi Anonymous ,
The return value of DATEADD() is a table with a single column. You are using a date to compare with a table.
Try this:
TotalNeededforCommit2 = CALCULATE ( SUM ( WO2_WorkOrderMaterialDetail[QtyCommitted] ), WO2_WorkOrderMaterialDetail[OrderStatus] = "F" && WO2_WorkOrderMaterialDetail[WODueDate_CC] >= EDATE(TODAY(),-3) && WO2_WorkOrderMaterialDetail[WODueDate_CC] <= EDATE(TODAY(),6) )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
2 Replies
Hi Anonymous ,
The return value of DATEADD() is a table with a single column. You are using a date to compare with a table.
Try this:
TotalNeededforCommit2 = CALCULATE ( SUM ( WO2_WorkOrderMaterialDetail[QtyCommitted] ), WO2_WorkOrderMaterialDetail[OrderStatus] = "F" && WO2_WorkOrderMaterialDetail[WODueDate_CC] >= EDATE(TODAY(),-3) && WO2_WorkOrderMaterialDetail[WODueDate_CC] <= EDATE(TODAY(),6) )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum