cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cottrera
Post Partisan
Post Partisan

DAX count if dates < or >= relating date

Hi

 

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

 

Table 1 - Property (1 row per property)

Property RefAddressInspection DateDAX - repair count < repair dateDAX - repair count >= repair date
18100 Dax Road05/07/202141
3545 Query Street12/09/202116
57205 Folding Avenue05/07/202252

 

 

 

Table 1  - Repairs (Possibly many repairs per property)

Job RefProperty RefTradeRepair Date
1387918ELEC11/03/2021
1731218PLUM13/03/2021
1640718ELEC20/03/2021
1921618CARP04/07/2021
1608518PLUM18/08/2021
1623235CARP08/09/2021
1703935ELEC12/09/2021
1869135PLUM10/11/2021
1802335ELEC13/11/2021
1253035PLUM08/12/2021
1305635ELEC02/02/2022
1999335ELEC22/03/2022
1777257ELEC24/03/2022
1614357ELEC30/03/2022
1504557CARP19/05/2022
1435257ELEC28/05/2022
1020957CARP04/07/2022
1211457CARP18/07/2022
1436757CARP25/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 RefAddressInspection DateDAX - repair count < repair dateDAX - repair count >= repair date
18100 Dax Road05/07/202141
3545 Query Street12/09/202116
57205 Folding Avenue05/07/202252

 

thank you

 

RIchard

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

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] )
    )
)

vzhangti_0-1669368452617.png

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.

View solution in original post

6 REPLIES 6
v-zhangti
Community Support
Community Support

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] )
    )
)

vzhangti_0-1669368452617.png

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.

Thank you😀

cottrera
Post Partisan
Post Partisan

Hi  , here is my model

cottrera_0-1669356498986.png

 

cottrera
Post Partisan
Post Partisan

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

cottrera_0-1669301692332.png

Richard

@cottrera what does your model look like?

PaulOlding
Super User
Super User

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])
    )
)

PaulOlding_0-1669301078420.png

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors