Problem with Creating new column based on multiple coditions

[Jan-H]

I need to generate a column in Maintenance that equals 1 if:

1. Car is registered in 2010 (other table linked to maintenance table)
2. Maintenance is done in for the first time in that year (if a car has multiple maintenances in a year it should only show a 1 for the first maintenance of that year)

@v-jayw-msft 

[v-rzhou-msft · Accepted solution]

Hi @Jan-H 

Car Table

Add a Year column in Matintenance Table.

Year = YEAR(Maintenance[Date of Maintenance])

Then try this code.

Flag 1 =
VAR _MinDateEachYear =
    CALCULATE (
        MIN ( Maintenance[Date of Maintenance] ),
        FILTER ( Maintenance, Maintenance[Year] = EARLIER ( Maintenance[Year] ) )
    )
RETURN
    IF (
        AND (
            RELATED ( Car[Registration year] ) = 2010,
            Maintenance[Date of Maintenance] = _MinDateEachYear
        ),
        1,
        BLANK ()
    )

Result is as below.

Best Regards,
Rico Zhou

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

[jppv20]

Hi @Jan-H ,

Can you provide some example data from your table?