Date DAX - Consider dates up to 5th of next month? | Consider dates in current month?

[archuleta28]

Hi, I am into learning phase of DAX and need your urgent help!

I have a report that calculates Values and Quantity based on some conditions. User will select month from slicer so the data should populate for that selected month only.

Requirement 1:

I need to calculate Quantity and Values with this condition:  Date1 is not blank+ Order date is in current month + Custom number is blank

Now, how to write DAX for this logic that considers Order dates only for the selected month? For example, user has selected May month so this DAX should only consider Order dates from May month for above mentioned condition.

Requirement 2:

Here also, I need to calculate Quantity and Value with this condition: Date1 is not blank+ Consider ETD up to 5th of next month + Custom Number is blank

How to write DAX for this logic that considers ETD (dates) upto 5th of next month? For example, user has selected May month so this DAX should consider ETD from 5th May to 5th June i.e. 5th of the next month.  

Desired output in table for both conditions:

Qty	Value
10	2000

Please let me know, any additional clarification is required from my end.  Any help would seriously be a big help! 

[JunaidSayyed]

Great Job!!👏

[archuleta28]

Sample data is:

OrderDate	SONo	QTY	Date1	ETD	Custom Number	Value
29-08-2022	645	108		02-09-2022	9O1308	4589
10-10-2022	1006	935		10-12-2022	9O1945	4700
11-09-2022	507	135		06-10-2022	9O0552	3088
10-05-2022	70	15		31-06-2022	R9176	4030
10-08-2022	2523	84	28-04-2022	14-09-2022	9O0588	2794
	79	13	28-04-2022	07-04-2022	9O0588	4223
10-10-2022	270	45.2		05-11-2022	9O0488	4257
10-11-2022	507	35		16-12-2022	9O0552	4264
18-04-2022	1231	81	30-05-2022	01-06-2022	9O0571	4288
11-06-2022	500	0.1	15-06-2022	02-07-2022	9O0803	3198
08-07-2022	552	49.1	08-06-2022	30-08-2022	9O0685	2138

[amitchandak · Accepted solution]

@archuleta28 , Try measure like

 Date1 is not blank+ Order date is in current month + Custom number is blank

calculate( Sum(Table[Qty]), filter(Table, not(isblank(Table[Date1])) && eomonth([Order Date],0) = eomonth(today(),0)  && isblank(Table[Custom Number]) ) )

 Date1 is not blank+ Consider ETD up to 5th of next month + Custom Number is blank

calculate( Sum(Table[Qty]), filter(Table, not(isblank(Table[Date1])) && eomonth([ETD],0) >= (eomonth(today(),-1) +1) && eomonth([ETD],0) <= (eomonth(today(),0) +5)   && isblank(Table[Custom Number]) ) )

[archuleta28]

Thanks @amitchandak for your swift solution! Your first solution worked and I am working on the second solution you have provided. If anything comes up, I'll connect with you again 🙂

Your help is much appreciated!