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

 

 

 

When uploading my data to Power BI desktop the format of the timestamp is changing to 08.05.2025 13:48:28

 

 

Unfortnetly, I can not change the format with this in power bi

 

 

I need the information of the timestamp, because I am calculating the time difference between two timestamps as a new raw.

 

 

Zeitdifferenz =
VAR AktuellerZeitpunkt = 'Maschinenstatus Schleifen'[Zeitstempel]
VAR Maschine = 'Maschinenstatus Schleifen'[Maschine]
VAR VorherigerZeitpunkt =
    CALCULATE(
        MIN('Maschinenstatus Schleifen'[Zeitstempel]),
        FILTER(
            'Maschinenstatus Schleifen',
            'Maschinenstatus Schleifen'[Maschine] = Maschine &&
            'Maschinenstatus Schleifen'[Zeitstempel] > AktuellerZeitpunkt
        )
    )
VAR Dauer =VorherigerZeitpunkt - AktuellerZeitpunkt
RETURN
IF(
    ISBLANK(VorherigerZeitpunkt),
    BLANK(),
    FORMAT(Dauer, "hh:mm:ss")
)

 

 

 

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.

8 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    hish , Try using

    Zeitdifferenz =
    VAR AktuellerZeitpunkt = 'Maschinenstatus Schleifen'[Zeitstempel]
    VAR Maschine = 'Maschinenstatus Schleifen'[Maschine]
    VAR VorherigerZeitpunkt =
    CALCULATE(
    MIN('Maschinenstatus Schleifen'[Zeitstempel]),
    FILTER(
    'Maschinenstatus Schleifen',
    'Maschinenstatus Schleifen'[Maschine] = Maschine &&
    'Maschinenstatus Schleifen'[Zeitstempel] > AktuellerZeitpunkt
    )
    )
    VAR Dauer = VorherigerZeitpunkt - AktuellerZeitpunkt
    RETURN
    IF(
    ISBLANK(VorherigerZeitpunkt),
    BLANK(),
    FORMAT(Dauer, "hh:mm:ss")
    )

    • hish's avatar
      hish
      Frequent Visitor
      1 year ago

      It is exactly my code with no changes. It is still not working

  • 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.

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

    Hi hish ,

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

    Regards,

    Chaithanya

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

    Hi @hish ,

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

    Regards,

    Chaithanya

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

    Hi @hish ,

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

    Regards,

    Chaithanya