Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculated column for date comparison

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 nameStart dateEnd date
Zagrodno01/01/202131/12/2021
Luntas  
Gangarampur01/01/202131/12/2021
Luntas 31/12/2021
Frei Paulo01/01/202131/12/2021
Zagrodno01/01/202131/12/2021
Bellegarde01/08/202130/06/2022
Cosamaloapan de Carpio01/08/202130/06/2022
Seedorf01/08/2021 
Gangarampur01/08/202131/01/2022
Peter Yuan20/09/202231/12/2021
Rea Dorf  
Jin Sand19/09/202101/02/2021
Clive Barker16/11/202116/12/2021
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Jihwan_Kim
      Super User
      3 years ago

      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's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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"
)
  • Thennarasu_R's avatar
    Thennarasu_R
    Responsive Resident
    3 years ago

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