Forum Discussion

cottrera's avatar
cottrera
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

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 Ref Address Inspection Date DAX - repair count < repair date DAX - ...
Nee help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

PaulOlding's avatar
PaulOlding
Icon for Solution Sage rankSolution Sage
3 years ago

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