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

Hi johnt75 tamerj1,

 

Thank you both for your help. The DAX now has no errors, but it is returning the value for each row as -45658. 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Anonymous 

How does your data model look like?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tamerj1 ,

    What would you like to see to best aid you? Thanks

    • tamerj1's avatar
      tamerj1
      Community Champion
      3 years ago

      Anonymous 

      When a formula refers to multiple columns from multiple tables then I need to see the relationships of the model and the filter context of the visual. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi tamerj1,

        I hope this helps - screenshoot shows most/all relevant tables.

        Notes on most relevant connections:

        • 'Project'[PPProjectID] - 'HSE Inspection'[ProjectID]
        • 'Project'[BusinessUnitID] - 'Business Unit'[BusinessUnitID]
        • 'Project'[ProjectPlusCompletionDateInteger] - 'Date of Completion'[DateAsInteger]
        • 'Project'[ProjectStatusID] - 'Project Status'[ProjectStatusID]
        • 'HSE Inspection'[DateHSEInspectionCreatedOnInteger] - 'Date of Inspection'[DateAsInteger]

        Filters on visual:
        BusinessUnit is not (blank) or Fitout. 

        ProjectName does not contain 'Test '.

        ProjectStatusName is Live. (Or ProjectStatusID is 2).

        InspectionTypeName is Formal Inspection (not (blank) or Site Inspection).