Forum Discussion

ravi_ch's avatar
ravi_ch
Helper I
4 years ago

How can we calculate dates difference

How can we calculate no. of days between two days like
2/12/2020 - 2/20/2020 = 8, 

2/20/2020 - 3/24/2020 = 32

like this

6 Replies

  • truptis's avatar
    truptis
    Community Champion

    ravi_ch -> You can also do using DAX.

    Step 1: Create index column starting with 1 (Transform data -> Index column -> Start from 1)
    Step 2: using DAX create a new column

    PreviousRow = Lookupvalue(tablename[Date], tablename[index_column], tablename[index_column] -1)
    Step 3: Create another column for counting the difference

    Count = DATEDIFF([Date], [PreviousRow], DAY)

    ravi_ch -> Mark this as a solution & hit the thumbs up, if it works for you. Thanks.

  • truptis's avatar
    truptis
    Community Champion

    Hi ravi_ch ,

    You can do this is Power Query. 

    Step 1- Create an index column.
    Step 2 - Add new custom column 

    paste this code in it:
    = try #"Added Index"{[Index]-1} [Date] otherwise null 

    and then click on OK.
    this step is going to take the date present in 2nd row to the first row.

     Step 3: Create another column which has your result

    Result = DATEDIFF([date], [new column that we created], DAY)
    ravi_ch -> If this works, the  please mark it as a solution.

  • ravi_ch 

    you can create a measure

    Measure = 
    VAR _last=maxx(FILTER(ALL('Table'),'Table'[Date]<max('Table'[Date])),'Table'[Date])
    return DATEDIFF(_last,max('Table'[Date]),DAY)

  • truptis's avatar
    truptis
    Community Champion

    Hi ravi_ch , Please try this:
    Step 1: create index column using dax:
    Index_column = RANKX(ALL('Tablename'),[Date])

    Step 2: using DAX create a new column

    PreviousRow = Lookupvalue(tablename[Date], tablename[index_column], tablename[index_column] -1)
    Step 3: Create another column for counting the difference

    Count = DATEDIFF([Date], [PreviousRow], DAY)

    ravi_ch  -> Mark this as a solution & hit the thumbs up, if it works for you. Thanks.

  • but apply slicer like product mam it won't working.
    like for each Product what is difference in days between each sale

     

    • ryan_mayu's avatar
      ryan_mayu
      Super User

      ravi_ch 

      Pls try this

      Measure = 
      VAR _last=maxx(FILTER(ALL('Table'),'Table'[Date]<max('Table'[Date])),'Table'[Date])
      VAR _DF= DATEDIFF(_last,max('Table'[Date]),DAY)
      VAR _last2=maxx(FILTER(ALL('Table'),'Table'[Date]<max('Table'[Date])&&'Table'[Product]=max('Table'[Product])),'Table'[Date])
      VAR _DF2=DATEDIFF(_last2,max('Table'[Date]),DAY)
      RETURN IF(ISFILTERED('Table'[Product]),_DF2,_DF)

      pls see the attachment below.