Compare all values in column from one table with single value from other table

[Anonymous]

Hi, 

I've the next doubt: suppose that I have these tables, Dates and Projects:

Dates

Date

1981-01-01

1992-02-02

1993-03-03

2018-04-04

2019-04-04

Projects

Identifier     Name      Creation_date

ID1              Name1    1992-09-09

ID2              Name2    2018-02-02

What I want to get is the number of dates from Dates that are previous to Creation_date in Projects. In this example, for ID1 the value would be 2 (1981-01-01 and 1992-02-02) and for ID2 the value would be 3 (1981-01-01, 1992-02-02 and 1993-03-03). How can I achieve this?

Thank you for your help 🙂

[PattemManohar]

@Anonymous  Please add a New Column in Projects Table as below

Cnt = COUNTROWS(FILTER(Test305Dates,[Date]<Test305Projects[CreationDate]))

[Anonymous]

Hi @PattemManohar . First of all, thanks for your early response. I have a doubt related to Date field. How can I invoke it? Shoud I invoke it as Dates[Date]? In that case, PowerBI alerts me that a single value for Dates[Date] cannot  be determined. What's wrong with this?

[v-lili6-msft]

hi, @Anonymous 

If you could just use this formula to add a new column

Result = COUNTROWS(FILTER(Dates,Dates[Date]<Projects[Creation_date]))

If not your case, please share your sample pbix file.

Best Regards,

Lin

[PattemManohar]

@Anonymous  Could you please post the screenshot of the error message. Yes, it is Dates[Date]. I just referenced as [Date] as it is within the same Filter Context.