Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

time calculation

Dear all, I have a question about the following. Below is a small table with date-times. I want to show the date_time which is the closest to 07:15. Both the closest before 07:15 and the closest ...
Need Help
  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    5 years ago

    Try this:

     

    Before 7:15 =
CALCULATE (
    MAX ( [time_executed] ),
    FILTER (
        YourTable[time_executed],
        YourTable[time_executed] < 'Calendar'[Date] + 0.302083333333333
    )
)

     

     

     

    After 7:15 =
CALCULATE (
    MIN ( [time_executed] ),
    FILTER (
        YourTable[time_executed],
        YourTable[time_executed] > 'Calendar'[Date] + 0.302083333333333
    )
)

     

    P.S.  0.302083333333333 is the numerical value of 7:15 AM

     

Mohammad_Refaei's avatar
Mohammad_Refaei
Solution Specialist
5 years ago

Try this:

 

Before 7:15 =
CALCULATE (
    MAX ( [time_executed] ),
    FILTER (
        YourTable[time_executed],
        YourTable[time_executed] < 'Calendar'[Date] + 0.302083333333333
    )
)

 

 

 

After 7:15 =
CALCULATE (
    MIN ( [time_executed] ),
    FILTER (
        YourTable[time_executed],
        YourTable[time_executed] > 'Calendar'[Date] + 0.302083333333333
    )
)

 

P.S.  0.302083333333333 is the numerical value of 7:15 AM

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Mohammad_Refaei 

Is there a way to calculate the numeric value of a certain time?

  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    Solution Specialist
    5 years ago

    Yes... The easiest way is to enter it in Excel cell then change the cell format to decimal 🙂