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 
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 ) )
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

tamerj1 ,

I don't suppose you could help me with the DAX for the difference between the last inspection date and today for each project in this case? Thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    tamerj1 ,

    Amazing, thank you so much

  • 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 )
    )
    )
    )