Forum Discussion

hish's avatar
hish
Frequent Visitor
1 year ago
Solved

Calculating duration between two timestamps with millisecounds

Hello everyone,   I am new to Power BI. I am trying to calculate the time difference between two timestamps.   In Power Query my timestamp has the following format: 2025-05-13T13:48:28.4970000  ...
Need Help
Show and Tell
Tips and Tricks
  • v-kathullac's avatar
    v-kathullac
    1 year ago

    Hi hish ,

     

    can you try with below updated dax and let us know if you need anything else i think it will solve your issue

     

    TimeDifferenceFormatted =
    VAR CurrentTime = 'Machine Status Loops'[Zeitstempel]
    VAR CurrentMachine = 'Machine Status Loops'[Maschine]
    VAR NextTime =
    CALCULATE (
    MIN('Machine Status Loops'[Zeitstempel]),
    FILTER (
    'Machine Status Loops',
    'Machine Status Loops'[Maschine] = CurrentMachine &&
    'Machine Status Loops'[Zeitstempel] > CurrentTime
    )
    )
    VAR Duration = NextTime - CurrentTime
    VAR TotalMs = Duration * 86400000
    VAR Seconds = INT(TotalMs / 1000)
    VAR MilliSeconds = MOD(TotalMs, 1000)
    RETURN
    IF (
    ISBLANK(NextTime),
    BLANK(),
    FORMAT(Seconds, "00") & "." & FORMAT(MilliSeconds, "000") & " sec"
    )

     

    Regards,

    Chaithanya.

v-kathullac's avatar
v-kathullac
Icon for Community Support rankCommunity Support
1 year ago

Hi hish ,

Thank you for reaching out to Microsoft Fabric Community Forum.

can you try with below dax and let us know if you need anything else i think it will solve your issue

 

Time Difference =
VAR CurrentTime = 'Machine Status Loops'[Zeitstempel]
VAR CurrentMachine = 'Machine Status Loops'[Maschine]
VAR NextTimestamp =
CALCULATE(
MIN('Machine Status Loops'[Timestamp]),
FILTER(
'Machine Status Loops',
'Machine Status Loops'[Maschine] = CurrentMachine &&
'Machine Status Loops'[Zeitstempel] > CurrentTime
)
)
VAR Duration = NextTimestamp - CurrentTime
RETURN
IF(
ISBLANK(NextTimestamp),
BLANK(),
FORMAT(Duration, "hh:mm:ss")
)

 

Regards,

Chaithanya

  • hish's avatar
    hish
    Frequent Visitor
    1 year ago

    This code does still not show me the time differences in milisecounds

    • v-kathullac's avatar
      v-kathullac
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi hish ,

       

      can you try with below updated dax and let us know if you need anything else i think it will solve your issue

       

      TimeDifferenceFormatted =
      VAR CurrentTime = 'Machine Status Loops'[Zeitstempel]
      VAR CurrentMachine = 'Machine Status Loops'[Maschine]
      VAR NextTime =
      CALCULATE (
      MIN('Machine Status Loops'[Zeitstempel]),
      FILTER (
      'Machine Status Loops',
      'Machine Status Loops'[Maschine] = CurrentMachine &&
      'Machine Status Loops'[Zeitstempel] > CurrentTime
      )
      )
      VAR Duration = NextTime - CurrentTime
      VAR TotalMs = Duration * 86400000
      VAR Seconds = INT(TotalMs / 1000)
      VAR MilliSeconds = MOD(TotalMs, 1000)
      RETURN
      IF (
      ISBLANK(NextTime),
      BLANK(),
      FORMAT(Seconds, "00") & "." & FORMAT(MilliSeconds, "000") & " sec"
      )

       

      Regards,

      Chaithanya.