topic Re: Average Days Between Dates in DAX Commands and Tips

Average Days Between Dates

Anonymous — Mon, 20 Feb 2023 17:24:31 GMT

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.

Re: Average Days Between Dates

Greg_Deckler — Mon, 20 Feb 2023 17:27:54 GMT

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 - __Previous

Re: Average Days Between Dates

tamerj1 — Mon, 20 Feb 2023 19:00:57 GMT

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 )

Re: Average Days Between Dates

Anonymous — Wed, 22 Feb 2023 14:55:56 GMT

Hi 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

Re: Average Days Between Dates

Anonymous — Wed, 22 Feb 2023 14:21:06 GMT

Hi tamerj1 ,

This is still returning all rows as '27/12/1774' unfortunately!

Re: Average Days Between Dates

tamerj1 — Wed, 22 Feb 2023 14:25:08 GMT

Anonymous

Please share a sampler pbix file to work with.