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