Filtering / measures on first and last date

[KatkaS]

Hello,

could anyone please help me with following?

From the following table I have to find out if the Approval was done before 4PM...This is easy, the problem is that sometimes the approval occurs several times (like in below example by entity AUC)...

Could you please help me with two following things:

1. How to filter / calculate how to meaure what approval was first and what approval was last - or even better if the first was before 4PM and last before 4 PM?

2. How to calculate difference between first and last approval..?

Thank you very much!!

[hansei]

@KatkaSplease mark as solution, so as to help others

[hansei]

First = CALCULATE( MIN( 'TBL'[UPDATE TIME] ), FILTER( ALL( 'TBL' ), [ENTITY] = SELECTEDVALUE( TBL[ENTITY] ) ) )

Last = CALCULATE( MAX( 'TBL'[UPDATE TIME] ), FILTER( ALL( 'TBL' ), [ENTITY] = SELECTEDVALUE( TBL[ENTITY] ) ) )

First Before 4 = ( [First] - INT( [First] ) ) < 2/3
Last Before 4 = ( [Last] - INT( [Last] ) ) < 2/3
Both Before 4 = [First Before 4] && [Last Before 4]
Difference = DATEDIFF( [Last], [First], MINUTE )

[KatkaS]

Thank you, Hansei!

I'm trying to add your measures to my power bi and have few more questions..

1. A stupid one - could you please explain what <2/3 means in measure First Before 4 = ( [First] - INT( [First] ) ) < 2/3?

2. How can I change False and True into Yes / No?

3. How can I change the difference of time into time..? I tried in Modeling - Format, but the option Date Time is Greyed out..

Thank you very much!

[hansei]

1. DAX represents datetime values with the date part as an integer, and the time part as a fraction of one. So, the first part of the equation removes the integer portion for comparison. 4pm is the 16th hour of the day, and can be represented as 16/24 or 2/3. So comparing the fractional part of a datetime to 2/3 will show before, after, or at 4pm, while ignoring the actual date.
2. As to Yes/No, just change the measure to an IF function e.g.

First Before 4 = IF( ( [First] - INT( [First] ) ) < 2/3, "Yes", "No" )​

3. That measure is returning the difference in minutes. If you want a duration instead, change the measure to

Difference = [Last] - [First]​

[KatkaS]

Thank you very much, Hansei!

I still have few more questions...

1.  It seems that the system is confused how to calculate the first and last approval, if the approval was done only once..

there is a mistake when the approval was not done on time and done only once: example for PLL and NNC -system marks it as Yes was First approval and No for Last approval but it is not true - it was only one approval and it was Late.

2. There is a mistake also for PRC - it marks two approvals as Late, but actually one is Before 4, second is After..

3. It leads also in mistake in Difference - now that I changed it to [Last] - [First]​, it returns the Date format which I'm not able to change..

Is there a way how to instruct the system that if there is only one approval, then return the same value or something like this?