Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have following table:
Date | DayOfTheWeek | TaskDone | Feedback | FlagDone | ||||||
06.11.2022 | Sunday | Yes | 06.11.2022 22:30 | |||||||
07.11.2022 | Monday | Yes | ||||||||
08.11.2022 | Tuesday | Yes | 08.11.2022 22:45 | Yes |
I need a flag like the one in red. Every night someone needs to do a a task. The problem with this task is it can happen anywhere from 22:00PM until 02:00AM. I want to record if it was done. So since the day switches to next day and people start their first shift of the week at sunday night, I want to handle Sundays like Mondays. So if I have a Feedback time returned, I want the FlagDone to return a yes for the Monday row and the Monday Night for the Tuesday row ....and so fourth.
Thank you very much in advance.
Best.
Solved! Go to Solution.
As I thought, the time for "Feedback" was incorrect in the original post.
I have a solution that requires 2 calculated columns and operates on the assumption that "Feedback" entries are only made from 22:00 - 2:00 window. You can probably modify the nested IF to fit your logic however.
First Column - "FlagDate" where 'Table' is the original table. This checks feedback hour and either adds a day to "Date" or leaves it alone depending on if the hour is >= 22.
Hi @Applicable88 ,
Could you please provide some special examples to explain the backend logic of the column [FlagDone]? When it can be marked as "Yes"? If the value of field [Feedback] is not null and the time range is from 22:00 PM to 02:00 AM, then it mark as "Yes"? Thank you.
Best Regards
@v-yiruan-msft @pbi-novice , thanks for the reply. I will make a more precise explanation what I try to achieve here.
There is one task "CheckMachine" at night that needs to be done. Since the nightshift is where the day switch to next day, its making that flag a little more complicated. When the task is done by emloyee after 22PM or until 02AM in the morning of next day the calculated "Flag Done" column should return okay for that next day.
So I have two examples below. At sunday night "CheckMachine" was done at 22:30PM so it counts as "Done" for the next day which is Monday.
The second example is when it was done after midnight, so it Flag should return "Done" for the same day.
Date | DayOfTheWeek | Task | Timestamp | TaskDone | FlagDone | |||||
06.11.2022 | Sunday | CheckMachine | 6.11.22 22:30 | Yes | ||||||
07.11.2022 | Monday | Yes | ||||||||
08.11.2022 | Tuesday | CheckMachine | 8.11.22 1:30 | Yes | Yes | |||||
Hope I explained my problem more clearly now.
Thank you very much in advance.
Best.
As I thought, the time for "Feedback" was incorrect in the original post.
I have a solution that requires 2 calculated columns and operates on the assumption that "Feedback" entries are only made from 22:00 - 2:00 window. You can probably modify the nested IF to fit your logic however.
First Column - "FlagDate" where 'Table' is the original table. This checks feedback hour and either adds a day to "Date" or leaves it alone depending on if the hour is >= 22.
Am I correct in thinking that the last row should not be a "Yes"? Since the feedback date is >= 22H?
@pbi-novice , yes exactly.
For example if I did the task on Sunday from 22PM until midnight, then mark a "YES" on Monday Date.
Also return "YES" if its done a little bit later on Monday night from midnight until 2:00am.
And this logic for every day.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |