Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Power BI Logic Help

I have a set of date columns in a table where i need to showcase the timeline progression based on the order of columns.

Suggest me the logic and the related chart for it.
ex: date 2-date 1 = days ,
date 3- date 2 = days

DatesDataType
Date 1Date Time
Date 2Date Time
Date 3Date Time
Date 4Date Time
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,Hello PijushRoy ,

    Thank you for your prompt reply.

     

    Based on my understanding, you want to calculate difference between dates as days.

     

    Per my test, we can create an index column from 1 in power query for sorting your Date column:

    Then create a measure using the following code to meet your requirement:

     

     

    DateDiff = VAR currentIndex = SELECTEDVALUE('Table'[Index])
VAR currentDate=CALCULATE(SELECTEDVALUE('Table'[Date]),FILTER(ALL('Table'),'Table'[Index]=currentIndex))
VAR preDate=IF(currentIndex=1,CALCULATE(SELECTEDVALUE('Table'[Date]),FILTER(ALL('Table'),'Table'[Index]=1)),
CALCULATE(SELECTEDVALUE('Table'[Date]),FILTER(ALL('Table'),'Table'[Index]=currentIndex-1)))
RETURN
DATEDIFF(preDate,currentDate,DAY)

     

     

    Result for your reference:

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

     

    Best regards,

     

    Joyce

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • PijushRoy's avatar
    PijushRoy
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Anonymous 
    The requirement is not clear to me.
    Can you explain more, what is your initial data and what is output you are looking for.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,Hello PijushRoy ,

    Thank you for your prompt reply.

     

    Based on my understanding, you want to calculate difference between dates as days.

     

    Per my test, we can create an index column from 1 in power query for sorting your Date column:

    Then create a measure using the following code to meet your requirement:

     

     

    DateDiff = VAR currentIndex = SELECTEDVALUE('Table'[Index])
VAR currentDate=CALCULATE(SELECTEDVALUE('Table'[Date]),FILTER(ALL('Table'),'Table'[Index]=currentIndex))
VAR preDate=IF(currentIndex=1,CALCULATE(SELECTEDVALUE('Table'[Date]),FILTER(ALL('Table'),'Table'[Index]=1)),
CALCULATE(SELECTEDVALUE('Table'[Date]),FILTER(ALL('Table'),'Table'[Index]=currentIndex-1)))
RETURN
DATEDIFF(preDate,currentDate,DAY)

     

     

    Result for your reference:

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

     

    Best regards,

     

    Joyce

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.