Forum Discussion

KatkaS's avatar
KatkaS
Post Patron
6 years ago

Filtering / measures on first and last date

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!!

 

 

Need Help

5 Replies

  • hansei's avatar
    hansei
    Helper V
    6 years ago
    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's avatar
      KatkaS
      Post Patron
      6 years ago

      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's avatar
        hansei
        Helper V
        6 years ago
        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]​