Creating measure that counts Widgets if their Parts arrival date is only within the current month

[RustyNails]

Slightly complicated to explain, but it's a simple request actually! Lets say I am trying to build widgets. Each widget receives multiple "Bill of Materials" (think of it as parts for the widgets, split into multiple orders) in order to complete building the widget. What I'm trying to do is only count the widgets who's EARLIEST Bill Of Materials arrival date is within the current month. As an example, say I'm building Widget A and it has 3 BillOfMaterials with the first one arriving in October and the 3rd one arriving in December. I DO NOT want to count this widget because it already received it's first BillOfMaterials order way back in October. Sample:

In my Sample above, I ONLY want to count Widget B once because it's earliest BillOfMaterials Arrival Date is in the current month. Please keep in mind that I have dozens of fields, so I will most definitely need to summarize my measure. This is what I have so far:

CALCULATE(
COUNTX(
SUMMARIZE(Table, Table[Widget_ID],  "cnt",

                          MIN(Table[BillOfMaterials_Arrival_Date])),

                          [cnt]

                     ),
FILTER ( Table, Month(Now()) = Month (Table[BillOfMaterials_Arrival_Date]) )
)

Bonus: Say once I have this measure created, I want to create a similar measure that does the same thing except only count the widgets if they received their BillOfMaterials in PREVIOUS month (instead of current month). Any help is appreciated. 

[RustyNails · Accepted solution]

I got it! Added some tweaks to @amitchandak 's DAX. Final Working DAX:

Measure1 = 

CALCULATE(
DISTINCTCOUNT(Table[Widget ID]),
FILTER(Table,
CALCULATE(MONTH(MIN(Table[BillOfMaterials_Arrival_Date])) = MONTH(NOW()), ALLEXCEPT(Table,Table[Widget ID]))
),
NOT(ISBLANK(Table[BillOfMaterials_Arrival_Date]))

) + 0

The Bonus Measure is the same, except I flip the = sign to <.

Thank you all for your help!!!! hooray!!

[AllisonKennedy]

If you always want to focus on Earliest arrival date, you could add a new column to your table that flags this date: 

IsEarliestArrival =

IF(

COUNTROWS(FILTER('Table', 'Table'[BOM Arrival Date] <=EARLIER('Table'[BOM Arrival Date]) && 'Table'[Widget ID] = EARLIER('Table'[Widget ID]))) = 1, 1, 0)

Then you can apply this as a page level or visual level filter, and use Time Intelligence to get the count previous month. I like to use DATEADD and this assumes you have a date table connected to the BOM Arrival Date? 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

[RustyNails]

I tried to make the dimension, but it gives me zero for everything 😞

[amitchandak]

@RustyNails , You try measures like. All measure should follow this

measure = calculate(Min(Table[Billof material ID]), filter(Table,Table[BillOfMaterials_Arrival_Date] = calculate(Min(Table[BillOfMaterials_Arrival_Date]), allexpcept(Table,table[Widget ID]))))

measure = calculate(count(Table[Widget_ID]), filter(Table,Table[BillOfMaterials_Arrival_Date] = calculate(Min(Table[BillOfMaterials_Arrival_Date]), allexpcept(Table,table[Widget ID]))))

Or you can create a flag like this and filter on 1

flag =
var _min = minx(filter(Table, [BillOfMaterials_Arrival_Date] <=earlier([BillOfMaterials_Arrival_Date]) && [Widget ID] = earlier([Widget ID])),[BillOfMaterials_Arrival_Date])
return
if([BillOfMaterials_Arrival_Date] =_min,1,0)

[RustyNails]

Hello,

Unfortunately this didn't quite work.

The measure you have simply looks at whether there's an earlier BillofMaterials_Arrival_date regardless of which month, and that is not what I'm looking for. What I'm looking for is that for any particular Widget ID, if a BillOfMaterials_Arrival_Date already arrived in previous month, I want to Ignore that Widget ID from my counts. However, for any Widget ID that has a BillOfMaterials_Arrival_Date only for current month. I want to count that Widget. If you would like to imagine, a Widget ID has a monthly budget associated to it and the first time a BillOfMaterial_ID arrives, it hits our budget for that month only (regardless if it has more BillOfMaterial_ID's arriving in later months). First come first serve sort of a deal.

The part in your measure calculation which is calculate(Min(Table[BillOfMaterials_Arrival_Date]), allexpcept(Table,table[Widget ID])) will tell me the earliest BillOfMaterials_Arrival_Date. This is partially correct. I want to make sure that this earliest date is NOT falling in the previous month. When I Implement the measure I get this result:

This is not what I want based on my Original Sample. In my Sample, I only want to count Widget B because that's the only Widget that has had a BillOfMaterials_Arrival_Date in current month, which is December in our case. I apologize if I was unclear in my post. Thank you so much for your help. Much appreciated.