Average Days Between Dates

[BotBot1]

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.

[tamerj1]

Hi @BotBot1 

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 )

[BotBot1]

Hi @tamerj1 ,

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

[tamerj1]

@BotBot1 

Please share a sampler pbix file to work with. 

[Greg_Deckler]

@BotBot1 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/3395....
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

[BotBot1]

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