Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Average Days Between Inspections

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...
In
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    Please try

    AverageDaysBetweenInspections =
AVERAGEX (
    SUMMARIZE ( 'HSE Inspection', Project[ProjectName], 'Date of Inspection'[date] ),
    VAR CurrentDate = 'Date of Inspection'[date]
    VAR CurrentProjectTable =
        CALCULATETABLE (
            SUMMARIZE ( 'HSE Inspection', Project[ProjectName], 'Date of Inspection'[date] ),
            ALL ( 'Date of Inspection'[date] )
        )
    VAR TableBefore =
        FILTER ( CurrentProjectTable, 'Date of Inspection'[date] < CurrentDate )
    VAR PreviousDate =
        MAXX ( TableBefore, 'Date of Inspection'[date] )
    RETURN
        IF ( NOT ISEMPTY ( TableBefore ), INT ( CurrentDate - PreviousDate ) )
)
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Anonymous 

You may try

AverageDaysBtweenInspectionAndToday =
AVERAGEX (
VALUES ( Project[ProjectName] ),
INT (
TODAY ()
- CALCULATE (
MAX ( 'Date of Inspection'[date] ),
CROSSFILTER ( 'HSE Inspection'[DateHSEInspectionCreatedOnInteger], 'Date of Inspection'[DateAsInteger], BOTH )
)
)
)

Anonymous's avatar
Anonymous
Not applicable
3 years ago

tamerj1 ,

Amazing, thank you so much