Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Days Difference between two dates in same column with duplicate dates in Power BI

I have a column with duplicate dates. I need to calculate date difference based on another column.

For example, I have Stage column with 1,2,3... repeated values . What will be function I can use to calculate date values differences for stage 1?

Thanks in Advance..

  • Anonymous 

    maybe you can try this

    measure=calculate(max(lastdate),allexcept(table,bomno))-calculate(min(lastdate),allexcept(table,bomno))

  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks a lot. It worked for me...😊

18 Replies

  • Anonymous 

    pls provide some sample data and expected output.

    • Anonymous's avatar
      Anonymous
      Not applicable

      My sample data

       

      So, I need days difference between first date and last date (in lastdate Column) based on bomno column. 

      Thanks in advance..

       

      • ryan_mayu's avatar
        ryan_mayu
        Super User

        Anonymous 

        maybe you can try this

        measure=calculate(max(lastdate),allexcept(table,bomno))-calculate(min(lastdate),allexcept(table,bomno))

  •  

     

    Days Diff Measure : =
    VAR ranking =
    RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Stage] = MAX ( 'Table'[Stage] ) ),
    CALCULATE ( MAX ( 'Table'[Date] ) ),
    ,
    ASC
    )
    VAR result =
    DATEDIFF (
    CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER (
    ALL ( 'Table' ),
    'Table'[Stage] = MAX ( 'Table'[Stage] )
    && RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Stage] = MAX ( 'Table'[Stage] ) ),
    CALCULATE ( MAX ( 'Table'[Date] ) ),
    ,
    ASC
    ) = ranking - 1
    )
    ),
    MAX ( 'Table'[Date] ),
    DAY
    )
    RETURN
    result

     

     

    https://www.dropbox.com/s/rwbkhoz3ir6d789/erp.pbix?dl=0 

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    Thanks for your answers...

  • Augustinas's avatar
    Augustinas
    Frequent Visitor

    Hi all,

     

    please help me. I need to calculate the sum of several date time differences by different values (Case ID status code changes and it has date and time). It is complicated to explain, so please look there:

    Can anyone help? thanks in advance.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User

      Hi,

      This should ideally be written as a calculated column formula (not as a measure).  If you agree with my approach, then please share data in a format that can be pasted in an MS Excel file.

      • Augustinas's avatar
        Augustinas
        Frequent Visitor
        IDReceived date and timeNew ValueDate and time.
        187942024-05-06 14:19PAKLAUSIMAS5-7-2024 9:01:59 AM
        187942024-05-06 14:19CAD5-9-2024 11:37:04 AM
        187942024-05-06 14:19SĄMATOS RUOŠIMAS5-9-2024 11:56:39 AM
        187942024-05-06 14:19CAD5-9-2024 11:58:05 AM
        187942024-05-06 14:19SĄMATOS RUOŠIMAS5-9-2024 1:01:58 PM
        187942024-05-06 14:19MARŽOS NUSTATYMAS5-16-2024 4:56:39 PM
        187942024-05-06 14:19PASIŪLYMO RUOŠIMAS5-16-2024 5:06:06 PM
        187942024-05-06 14:19PASIŪLYMAS IŠSIŲSTAS5-20-2024 11:35:32 AM
        187432024-05-02 14:08PAKLAUSIMAS5-2-2024 3:12:14 PM
        187432024-05-02 14:08CAD5-2-2024 5:08:48 PM
        187432024-05-02 14:08SĄMATOS RUOŠIMAS5-3-2024 9:34:08 AM
        187432024-05-02 14:08PASIŪLYMO RUOŠIMAS5-3-2024 9:34:30 AM
        187432024-05-02 14:08CAD5-3-2024 11:11:37 AM
        187432024-05-02 14:08SĄMATOS RUOŠIMAS5-3-2024 1:21:30 PM
        187432024-05-02 14:08MARŽOS NUSTATYMAS5-7-2024 8:26:21 AM
        187432024-05-02 14:08PASIŪLYMO PATIKRINIM5-7-2024 10:04:43 AM
        187432024-05-02 14:08PASIŪLYMO RUOŠIMAS5-7-2024 5:27:10 PM
        187432024-05-02 14:08PASIŪLYMAS IŠSIŲSTAS5-7-2024 7:43:07 PM
        186912024-05-30 16:15PAKLAUSIMAS4-26-2024 11:15:04 AM
        186912024-05-30 16:15CAD4-26-2024 11:25:35 AM
        186912024-05-30 16:15SĄMATOS RUOŠIMAS4-26-2024 12:46:32 PM
        186912024-05-30 16:15MARŽOS NUSTATYMAS4-26-2024 8:14:19 PM
        186912024-05-30 16:15PASIŪLYMO RUOŠIMAS4-26-2024 8:15:53 PM
        186912024-05-30 16:15PASIŪLYMAS IŠSIŲSTAS4-26-2024 8:27:21 PM
        186912024-05-30 16:15SĄMATOS RUOŠIMAS4-29-2024 9:32:12 AM
        186912024-05-30 16:15MARŽOS NUSTATYMAS4-29-2024 1:55:51 PM
        186912024-05-30 16:15PASIŪLYMO RUOŠIMAS4-29-2024 2:07:03 PM
        186912024-05-30 16:15PASIŪLYMAS IŠSIŲSTAS4-29-2024 2:15:28 PM
        186912024-05-30 16:15SĄMATOS RUOŠIMAS5-2-2024 9:05:54 AM
        186912024-05-30 16:15MARŽOS NUSTATYMAS5-2-2024 11:38:19 AM
        186912024-05-30 16:15SĄMATOS RUOŠIMAS5-3-2024 12:09:02 PM
        186912024-05-30 16:15MARŽOS NUSTATYMAS5-3-2024 1:50:27 PM
        186912024-05-30 16:15PASIŪLYMO RUOŠIMAS5-3-2024 2:15:28 PM
        186912024-05-30 16:15PASIŪLYMAS IŠSIŲSTAS5-3-2024 3:26:07 PM

         

        Sorry, I do not know how to add excel file.