Forum Discussion

KatkaS's avatar
KatkaS
Icon for Post Patron rankPost 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 occu...
Need Help
KatkaS's avatar
KatkaS
Icon for Post Patron rankPost 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
Icon for Helper V rankHelper 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]​

 

  • KatkaS's avatar
    KatkaS
    Icon for Post Patron rankPost Patron
    6 years ago

    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?