Forum Discussion
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
- truptisCommunity 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 columnPreviousRow = Lookupvalue(tablename[Date], tablename[index_column], tablename[index_column] -1)
Step 3: Create another column for counting the differenceCount = DATEDIFF([Date], [PreviousRow], DAY)
ravi_ch -> Mark this as a solution & hit the thumbs up, if it works for you. Thanks. - truptisCommunity Champion
Hi ravi_ch ,
You can do this is Power Query.
Step 1- Create an index column.
Step 2 - Add new custom columnpaste this code in it:
= try #"Added Index"{[Index]-1} [Date] otherwise nulland 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. - ryan_mayuSuper User
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) - truptisCommunity 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 differenceCount = DATEDIFF([Date], [PreviousRow], DAY)
ravi_ch -> Mark this as a solution & hit the thumbs up, if it works for you. Thanks.
- ravi_chHelper I
but apply slicer like product mam it won't working.
like for each Product what is difference in days between each sale- ryan_mayuSuper User
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.