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 d...
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"
)
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" }
)