Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Applicable88
Impactful Individual
Impactful Individual

Calculated column Flag for earlier incidents

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. 

1 ACCEPTED 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.

 

FlagDate = IF(ISBLANK('Table'[Feedback]), BLANK(), IF(HOUR('Table'[Feedback]) >= 22, DATEADD('Table'[Date ].[Date], 1, DAY),'Table'[Date ].[Date]))
 
Second Column - "FlagDone". This checks the "FlagDate" column for the "Date".
 
FlagDone = IF(CONTAINS('Table', 'Table'[FlagDate], 'Table'[Date ]), "YES", "NO")
 
Here is what the data looks like. A "YES" will appear if the date from "Date" column has an entry, regardless of the "Feedback" column. See the first 2 rows. 
pbinovice_0-1668172941067.png

 

View solution in original post

5 REPLIES 5
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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.

 

FlagDate = IF(ISBLANK('Table'[Feedback]), BLANK(), IF(HOUR('Table'[Feedback]) >= 22, DATEADD('Table'[Date ].[Date], 1, DAY),'Table'[Date ].[Date]))
 
Second Column - "FlagDone". This checks the "FlagDate" column for the "Date".
 
FlagDone = IF(CONTAINS('Table', 'Table'[FlagDate], 'Table'[Date ]), "YES", "NO")
 
Here is what the data looks like. A "YES" will appear if the date from "Date" column has an entry, regardless of the "Feedback" column. See the first 2 rows. 
pbinovice_0-1668172941067.png

 

pbi-novice
Helper I
Helper I

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. 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.