DAX count if dates < or >= relating date

[cottrera]

Hi

I have two tables that are related via the Property Ref column.

Table 1 - Property (1 row per property)

Property Ref	Address	Inspection Date	DAX - repair count < repair date	DAX - repair count >= repair date
18	100 Dax Road	05/07/2021	4	1
35	45 Query Street	12/09/2021	1	6
57	205 Folding Avenue	05/07/2022	5	2

Table 1  - Repairs (Possibly many repairs per property)

Job Ref	Property Ref	Trade	Repair Date
13879	18	ELEC	11/03/2021
17312	18	PLUM	13/03/2021
16407	18	ELEC	20/03/2021
19216	18	CARP	04/07/2021
16085	18	PLUM	18/08/2021
16232	35	CARP	08/09/2021
17039	35	ELEC	12/09/2021
18691	35	PLUM	10/11/2021
18023	35	ELEC	13/11/2021
12530	35	PLUM	08/12/2021
13056	35	ELEC	02/02/2022
19993	35	ELEC	22/03/2022
17772	57	ELEC	24/03/2022
16143	57	ELEC	30/03/2022
15045	57	CARP	19/05/2022
14352	57	ELEC	28/05/2022
10209	57	CARP	04/07/2022
12114	57	CARP	18/07/2022
14367	57	CARP	25/09/2022

I require two DAX functions to count the number of repairs for each property ref

DAX FUNCTION 1 -  if the Repair date (repairs table) is less than (<) the Inspection date (property table)

DAX FUNCTION 2 if the Repair date (repairs table) is freaterthan or equal  (>=) to the Inspection date (property table)

Here is my expected results

Property table

Property Ref	Address	Inspection Date	DAX - repair count < repair date	DAX - repair count >= repair date
18	100 Dax Road	05/07/2021	4	1
35	45 Query Street	12/09/2021	1	6
57	205 Folding Avenue	05/07/2022	5	2

thank you

RIchard

[v-zhangti · Accepted solution]

Hi, @cottrera 

You can try the following methods.

Column:

repair count < repair date = 
CALCULATE (
    COUNT ( Repairs[Property Ref] ),
    FILTER (
        Repairs,
        [Repair Date] < EARLIER ( 'Property'[Inspection Date] )
            && [Property Ref] = EARLIER ( 'Property'[Property Ref] )
    )
)

repair count > repair date = 
CALCULATE (
    COUNT ( Repairs[Property Ref] ),
    FILTER (
        Repairs,
        [Repair Date] >= EARLIER ( 'Property'[Inspection Date] )
            && [Property Ref] = EARLIER ( 'Property'[Property Ref] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[cottrera]

Thank you😀

[cottrera]

Hi  , here is my model

[cottrera]

thank you for reponding so quickly. However I am receiving this error

Richard

[PaulOlding]

@cottrera what does your model look like?

[PaulOlding]

Here's a couple of measures with a similar pattern.  The RELATED function allows you to access columns on the 1 side of a relationship while iterating the table on the many side.

DAX - repair count < repair date = 
COUNTROWS(
    FILTER(Repairs,
        Repairs[Repair Date] < RELATED('Property'[Inspection Date])
    )
)

[DAX - repair count >= repair date] = 
COUNTROWS(
    FILTER(Repairs,
        Repairs[Repair Date] >= RELATED('Property'[Inspection Date])
    )
)