Sum Value Over Relative Date Range

[Anonymous]

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 -

TotalNeededforCommit2 = CALCULATE(SUM(WO2_WorkOrderMaterialDetail[QtyCommitted]), WO2_WorkOrderMaterialDetail[OrderStatus] = "F" && WO2_WorkOrderMaterialDetail[WODueDate_CC] >= DATEADD(Today(), -2, MONTH) &&  WO2_WorkOrderMaterialDetail[WODueDate_CC] <= DATEADD(Today(), 6, MONTH))

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

[Anonymous · Accepted solution]

Hi @Anonymous ,

The return value of DATEADD() is a table with a single column. You are using a date to compare with a table.

DATEADD

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

EDATE

Best Regards,
Gao
Community Support Team

If 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

[Anonymous]

Thank you much!