Count of 'Dates Less Than Another Date' from a Separate Table

[nabarkve]

Hello!

I have three tables from three separate data sources. Tables 2 and 3 share a Customer Code value, which I've connected using a de-duped Table 1. I am trying to add a column in Table 3 that would count the number of Doc Date values in Table 2 that are 'less than' the Invoice Date value in Table 3. Please see screenshot below for reference. I'm trying to create the 'Doc Dates Before Invoice Date' column.

As the relation from Table 2 to 3 is many-to-many, I can't seem to get this linked up to make this calculation happen. Any insight would be much appreciated!

Thanks,

Nik

[v-yuta-msft · Accepted solution]

@nabarkve ,

You can create a calculate column using dax below:

Doc Dates Before Invoice Date = 
VAR Current_Invoice_Date = Table3[Invoice Date]
VAR Current_Customer_Code = Table3[Customer Code]
VAR Row_Number = CALCULATE(COUNT(Table2[Customer Code]), FILTER(Table2, Table2[Customer Code] = Current_Customer_Code && Table2[Doc Date] < Current_Invoice_Date))
RETURN
IF(ISBLANK(Row_Number), 0, Row_Number)

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nabarkve]

@v-yuta-msft - Thank you so much! This just saved me so much time! This is exactly what I was looking to achieve.