Forum Discussion
Average Days Between Dates
Hi,
I am trying to get the average days between inspection dates. Below is a breakdown of current dates by project (with blue and green dots being 2 different projects).
Below is my attempted DAX for getting the days between inspections, with an average calculation for averages later on:
DaysBetweenInspectionsALL =
VAR CurrentProject = SELECTEDVALUE(Project[ProjectName])
VAR CurrentDate = SELECTEDVALUE('Date of Inspection'[date])
VAR LastInspectionDate =
CALCULATE(
MAX('Date'[Date]),
FILTER(
ALL(Project),
Project[ProjectName] = CurrentProject &&
SELECTEDVALUE('Date of Inspection'[date]) < CurrentDate))
RETURN
IF(LastInspectionDate = BLANK(), BLANK(), CurrentDate - LastInspectionDate)
However, this has returned all row values as '27/12/1774', even with whole number formatting. I belive this might be from using an incorrect field in the LastInspectionDate variable. Any help would be appreciated, thank you.
5 Replies
Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __PreviousHi Greg_Deckler ,
I tried writing the DAX for this but the EARLIER function wasn't letting me use any fields or functions - could you help with writing out the DAX for my case? The below is my attempt:
AverageDaysBetweenInspectionsALL = VAR _Current = CALCULATE(MAX('Date of Inspection'[date])) VAR _PreviousDate = MAXX(FILTER('Date of Inspection', 'Date of Inspection'[date] < EARLIER('Date of Inspection'[date])), 'Date'[Date]) VAR _Previous = MAXX(FILTER('Date of Inspection', 'Date of Inspection'[date]=_PreviousDate), CALCULATE(MAX('Date of Inspection'[date]))) RETURN _Current - _Previous
Thank you
Hi Anonymous
a quick fix could be
DaysBetweenInspectionsALL =
VAR CurrentProject =
SELECTEDVALUE ( Project[ProjectName] )
VAR CurrentDate =
SELECTEDVALUE ( 'Date of Inspection'[date] )
VAR LastInspectionDate =
CALCULATE (
MAX ( 'Date'[Date] ),
FILTER (
ALL ( Project ),
Project[ProjectName] = CurrentProject
&& CALCULATE ( SELECTEDVALUE ( 'Date of Inspection'[date] ) ) < CurrentDate
)
)
RETURN
IF ( LastInspectionDate = BLANK (), BLANK (), CurrentDate - LastInspectionDate )
