Forum Discussion
Date difference in same column with duplicate dates
Hi,
I have an Excel table ("Site Table") below of (1) location names, (2) dates of visits, and (3) difference between visits. Each visit has its own row/entry. I'm trying to calculate the difference (days) of visits for each site, and was able to do this in Excel in the "Date Difference" column:
When I try to replicate in Power BI for Site 1, I cannot get the 0 to show up in the second record for 8/13/2021. Rather than subtracting from the previous entry (the first record for Aug 13, 2021), it subtracts from the most recent date that is before Aug 13, 2021 (which is June 25, 2021):
This is my current DAX formula (from online video):
Date Diff =
VAR FirstDateSub =
CALCULATE(
MIN('SiteTable'[Completed Date]),
ALLEXCEPT('SiteTable','SiteTable'[Site Name]))
VAR DemoteSub =
CALCULATE(
MAX('SiteTable'[Completed Date]),
FILTER(
ALLEXCEPT('SiteTable','SiteTable'[Site Name]),
'SiteTable'[Completed Date]<EARLIER('SiteTable'[Completed Date])))
return
(DATEDIFF(DemoteSub,'SiteTable'[Completed Date],DAY))/12
Does anyone know how to revise the formula to show the 0? Thank you.
alam6 , yes that's right, add an Index Column via "Transform Data", then "Add Column", then "Index Column"
Then the DAX for the Calculated Column for [Date Diff] should be as per below. The [Index] field used in the Calculated Column DAX must be the name of the index column you created.
Date Diff = VAR vCompletedDatePriorRow = CALCULATE( MAX('Site Table'[Completed Date]) ,FILTER(ALLEXCEPT('Site Table', 'Site Table'[Site Name]) ,'Site Table'[Index] < EARLIER('Site Table'[Index]) ) ) RETURN DATEDIFF(vCompletedDatePriorRow, 'Site Table'[Completed Date], DAY)This gives me the correct results:
This will also work when there are 3 or 4 etc visits to a site on the same day.
Bear in mind that for this solution to work correctly with an index column, the spreadsheet must be sorted by SiteName and Completed date. If not, it may not work correctly.
4 Replies
alam6 , I think you will need to add a sequence number per site into the table in Power BI, and then use that sequence number field in the DAX measure.
Thanks Anonymous. By sequence number, are you referring to an index column?
alam6 , yes that's right, add an Index Column via "Transform Data", then "Add Column", then "Index Column"
Then the DAX for the Calculated Column for [Date Diff] should be as per below. The [Index] field used in the Calculated Column DAX must be the name of the index column you created.
Date Diff = VAR vCompletedDatePriorRow = CALCULATE( MAX('Site Table'[Completed Date]) ,FILTER(ALLEXCEPT('Site Table', 'Site Table'[Site Name]) ,'Site Table'[Index] < EARLIER('Site Table'[Index]) ) ) RETURN DATEDIFF(vCompletedDatePriorRow, 'Site Table'[Completed Date], DAY)This gives me the correct results:
This will also work when there are 3 or 4 etc visits to a site on the same day.
Bear in mind that for this solution to work correctly with an index column, the spreadsheet must be sorted by SiteName and Completed date. If not, it may not work correctly.
This has worked so far, thank you very much Anonymous!
