Sum column based on if date is between two dates

[ClemFandango]

Hi there,

I have the following tables, and i am trying to count the number of rows in 'Table 2' where 'Lost' = 1 and the data in 'Table 1' is the same month. I am trying to do this by adding a custom column to 'table 1' using DAX.

Table 1 - Example

MMYYDD = end of month

Table 2 Example:-

Example of data from Table 2

Expanded_Date	Lost
01/09/23	1
02/09/23	0
03/09/23	1
01/10/23	1
15/12/23	1

I am hoping to achieve the following in 'Table 1' by adding a calculated column called 'Lost':-

MMYYDD	Lost
30/09/23	2
31/10/23	1
30/11/23	0
31/12/23	1
31/01/24	0
29/02/24	0

Any ideas or help greatly appreciated!

CF

[amitchandak · Accepted solution]

@ClemFandango , a new column in dax in Table 1

countx(filter(Table2, eomonth(Table1[Date], 0)  = eomonth(Table2[date],0) && [Lost] =1), [Lost])

[ClemFandango]

@amitchandak Thank you massivley! You are a genius!!