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

tamerj1,

I apologise for not being clear/causing any confusion. The 'Date' table is not of great importance and I do not have a slicer for this either.

I am simply trying to get the difference in days between each inspection date for all projects. I eventually want to get the average days between inspections based on the project, which I believe will be categorised later on in a different DAX by getting the averages of days between inspections by project.

But ideally if possible, I would just like a DAX for the average days between inspections by project in one go!

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 
    It worked!! Amazing. Thank you so much for your help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    tamerj1 ,

    Amazing, thank you so much

  • 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

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