Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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())
@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?
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" }
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |