Flag Missing Rows Based on Date/Time

[BraedonCook]

Hi All,

I have a list that looks like this:

Bag Number	DateandTime	TransactionType
1	10/08/2020 8:00am	Sign Out
1	10/08/2020 2:00pm	Sign In
2	10/08/2020 9:00am	Sign Out
3	10/08/2020 10:00am	Sign Out
3	10/08/2020 4:00pm	Sign In
2	11/08/2020 9:00am	Sign Out
2	11/08/2020 3:00pm	Sign In

As you can see, bag number 2 was never signed back in,

What I am looking to do is flag, if a bag wasn't signed back in within 12 hours,

ie:

Bag Number	DateandTime	TransactionType	SignedIn?
1	10/08/2020 8:00am	Sign Out
1	10/08/2020 2:00pm	Sign In
2	10/08/2020 9:00am	Sign Out	FALSE
3	10/08/2020 10:00am	Sign Out
3	10/08/2020 4:00pm	Sign In
2	11/08/2020 9:00am	Sign Out
2	11/08/2020 3:00pm	Sign In

Any assistance with this would be greatly appreciated

(also I'm unsure if this was posted in the right section)

[v-rzhou-msft]

Hi @BraedonCook 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[BraedonCook]

Hi Rico,

While this didn't end up solving my issue, we have engaged a partner to assist with building out our solution,
Not sure if you could ever call this one resolved, but I can delete the post if needed?

[v-rzhou-msft]

Hi @BraedonCook 

Build a table like yours to have a test.

Build a calculated column:

Rank = RANKX(FILTER(ALL('Table'),'Table'[Bag Number]=EARLIER('Table'[Bag Number])),'Table'[DateandTime],,ASC)

Build a measure to achieve your goal:

Measure = 
VAR _a = MAX('Table'[DateandTime])
VAR _b = CALCULATE(MAX('Table'[DateandTime]),FILTER(ALL('Table'),'Table'[Rank]=MAX('Table'[Rank])+1&&'Table'[Bag Number]=MAX('Table'[Bag Number])))
RETURN
IF(DATEDIFF(_a,_b,HOUR)>12,"FALSE",BLANK())

Result:

 

You can download the pbix file from this link: Flag Missing Rows Based on Date/Time

Best Regards,

Rico Zhou

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

[amitchandak]

@BraedonCook , Try like

if(isblank(countx(filter(Table, [DateandTime] >=earlier([DateandTime]) && [DateandTime] <=earlier([DateandTime]) Time(12,0,0) && [TransactionType] ="Sign In"),[Bag Number])),blank(),"FALSE")

[BraedonCook]

Hi @amitchandak 

It's saying "syntax for 'Time' is incorrect"

[amitchandak]

@BraedonCook , try

if(isblank(countx(filter(Table, [DateandTime] >=earlier([DateandTime]) && [DateandTime] <=earlier([DateandTime]) + Time(12,0,0) && [TransactionType] ="Sign In"),[Bag Number])),blank(),"FALSE")