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 ) )
)
johnt75's avatar
johnt75
Super User
3 years ago

Try removing the SELECTEDVALUE from the filter and casting the result as an int

DaysBetweenInspectionsALL =
VAR CurrentProject =
    SELECTEDVALUE ( Project[ProjectName] )
VAR CurrentDate =
    SELECTEDVALUE ( 'Date of Inspection'[date] )
VAR LastInspectionDate =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        FILTER (
            ALL ( Project ),
            Project[ProjectName] = CurrentProject
                && 'Date of Inspection'[date] < CurrentDate
        )
    )
RETURN
    IF (
        LastInspectionDate = BLANK (),
        BLANK (),
        INT ( CurrentDate - LastInspectionDate )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi johnt75,

     

    Thank you for this. It is currently saying "cannot find name [date]" for the LastInspectionDate variable! Can this be fixed?

     

    • johnt75's avatar
      johnt75
      Super User
      3 years ago

      How about

      DaysBetweenInspectionsALL =
VAR CurrentProject =
    SELECTEDVALUE ( Project[ProjectName] )
VAR CurrentDate =
    SELECTEDVALUE ( 'Date of Inspection'[date] )
VAR LastInspectionDate =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        REMOVEFILTERS ( Project ),
        Project[ProjectName] = CurrentProject,
        'Date of Inspection'[date] < CurrentDate
    )
RETURN
    IF (
        LastInspectionDate = BLANK (),
        BLANK (),
        INT ( CurrentDate - LastInspectionDate )
    )
      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        johnt75 

        Seems like 'Date of Inspection' is at the many side od the relationship. 
        In this case either to CROSSFILTER-BOTH the relationship or MAXX ( RELATEDTABLE ) like

        DaysBetweenInspectionsALL =
        VAR CurrentProject =
        SELECTEDVALUE ( Project[ProjectName] )
        VAR CurrentDate =
        SELECTEDVALUE ( 'Date of Inspection'[date] )
        VAR LastInspectionDate =
        CALCULATE (
        MAX ( 'Date'[Date] ),
        FILTER (
        ALL ( Project ),
        Project[ProjectName] = CurrentProject
        && MAXX ( RELATEDTABLE ( 'Date of Inspection' ), 'Date of Inspection'[date] ) < CurrentDate
        )
        )
        RETURN
        IF (
        LastInspectionDate = BLANK (),
        BLANK (),
        INT ( CurrentDate - LastInspectionDate )
        )