Calculated column for date comparison

[Anonymous]

Hello,

I have the folllowing table and i am trying to add a calculated column with output based on:

1. If Start date <= End date, output: Valid dates

2. If Start date > End date, output: Invalid dates

3. If Start date and/or End date missing, output: Missing date/s

Any help is much appreciated!

Student name	Start date	End date
Zagrodno	01/01/2021	31/12/2021
Luntas
Gangarampur	01/01/2021	31/12/2021
Luntas		31/12/2021
Frei Paulo	01/01/2021	31/12/2021
Zagrodno	01/01/2021	31/12/2021
Bellegarde	01/08/2021	30/06/2022
Cosamaloapan de Carpio	01/08/2021	30/06/2022
Seedorf	01/08/2021
Gangarampur	01/08/2021	31/01/2022
Peter Yuan	20/09/2022	31/12/2021
Rea Dorf
Jin Sand	19/09/2021	01/02/2021
Clive Barker	16/11/2021	16/12/2021

[Thennarasu_R]

Check This one

Calculated Column=IF(Start date <= End date,"Valid dates",
IF(Start date > End date,"Ivalid Dates",IF(And(Start date=Blank(), End date=Blank()),
OR(Start date=Blank(), End date=Blank())

[Anonymous]

@Jihwan_Kim Thank you for your reply! I am trying to add a Card visualization to show the total number of Invalid dates and Missing dates. I tried to use the calculated column directly but i am not sure how to filter and show the Invalid and Missing dates only. Any idea how to do this?

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Count invalid or missing: =
CALCULATE (
    COUNTROWS ( Data ),
    Data[Status CC] IN { "Invalid dates", "Missing dates" }
)

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Status CC =
SWITCH (
    TRUE (),
    ISBLANK ( Data[Start date] ) || ISBLANK ( Data[End date] ), "Missing dates",
    Data[Start date] > Data[End date], "Invalid dates",
    Data[Start date] <= Data[End date], "Valid dates"
)