Forum Discussion

suji's avatar
suji
Advocate I
9 years ago
Solved

validate current timestamp between given start and end date times

Hi,

 

I have to validate current timestamp between given start and end date times on Power BI Desktop.

 

 

Thanks & Regards,

Suresh Peddirsi,

Need Help
  • Baskar's avatar
    Baskar
    9 years ago

    Don't worry dude we have another one way

     

    Time Difference =
    var Start_Date = NOW()  -- Replace Now() with start_date 
    var end_Date = NOW()    -- Replace Now() with end_date 

    var Start_time = FORMAT(Start_Date,"HH:mm:ss")
    var End_time = FORMAT(end_Date,"HH:mm:ss")

    return FORMAT( TIME(LEFT(Start_time,2) ,MID(Start_time,4,2),RIGHT(Start_time,2)) -
    TIME(LEFT(End_time,2) ,MID(End_time,4,2),RIGHT(End_time,2)) , "HH:mm:ss"
    )

     

     

    I hope it will give u the time difference dude..

     

     

    let me know any further doubts . cheers 

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi suji,

    What is the format of your timestamp column? Do you set the Data Type of the timestamp column to Date/Time? If you have changed its data type to Date/Time, you are able to directly check if current timestamp is between given start date and end date using formulas as follows.

    Column = if(Table3[Timestamp]-Table3[Start Date]>=0 && Table3[End Date]-Table3[Timestamp]>=0,1,0)
    MinColumn = if((24*60*(Table3[Timestamp]-Table3[Start Date])>=0) && (24*60*(Table3[End Date]-Table3[Timestamp]))>=0,1,0)


    You can review this similar blog about how to calculate difference between two dates in DAX.

    If the above example doesn’t return your expected result, please help to post sample data of your timestamp column and tell us what is the data type of this column.

    Thanks,
    Lydia Zhang

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    Datediff (Startdate,Enddate,Day)

     

    so use DateDiff function it will help u

    • suji's avatar
      suji
      Advocate I
      9 years ago

      Hi Baskar,

       

      Thanks for your quick response. Datediff will work for date compare. but my issue is Date with timestamp.

       

      I have to check current timestamp is between given start date and end date.

       

      Thanks & Regards,

      Suresh Peddirsi

      • Baskar's avatar
        Baskar
        Resident Rockstar
        9 years ago

        Don't worry dude we have another one way

         

        Time Difference =
        var Start_Date = NOW()  -- Replace Now() with start_date 
        var end_Date = NOW()    -- Replace Now() with end_date 

        var Start_time = FORMAT(Start_Date,"HH:mm:ss")
        var End_time = FORMAT(end_Date,"HH:mm:ss")

        return FORMAT( TIME(LEFT(Start_time,2) ,MID(Start_time,4,2),RIGHT(Start_time,2)) -
        TIME(LEFT(End_time,2) ,MID(End_time,4,2),RIGHT(End_time,2)) , "HH:mm:ss"
        )

         

         

        I hope it will give u the time difference dude..

         

         

        let me know any further doubts . cheers