Forum Discussion

EVIJ's avatar
EVIJ
Regular Visitor
6 years ago
Solved

How to calculate the difference between rows instead of between columns

Hi,

 

I would like to calculate the datedifference between rows, and use a calculated column for this. Is this possible in Power BI?

 

For example:

row 1: 4-2-2020

row 2: 8-2-2020

row 3: 15-2-2020

etc.

 

How do I calculate the difference in days between 4-2-2020 and 8-2-2020, between 8-2-2020 and 15-2-2020?

Thank you.

 

Kind regards, Ellen

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    EVIJ , you can have new column like

    diff =
    dateduff(maxx(filter(table,[Date]<earlier([Date])),[Date]),[Date],day)

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    EVIJ , you can have new column like

    diff =
    dateduff(maxx(filter(table,[Date]<earlier([Date])),[Date]),[Date],day)

    • EVIJ's avatar
      EVIJ
      Regular Visitor
      6 years ago

      Thank you very much!! It works!

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    6 years ago

    EVIJ 

    you can try this

     

    Column = 
VAR _max=MAXX(FILTER('Table','Table'[Column1]<EARLIER('Table'[Column1])),'Table'[Column1])

return DATEDIFF(_max,'Table'[Column1],DAY)

     

    • EVIJ's avatar
      EVIJ
      Regular Visitor
      6 years ago

      Thank you very much!! It works 🙂

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi EVIJ 

     

    If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

    If not, please kindly elaborate more.