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 DAX for getting the days between inspections, with an average calculation for averages later on:

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 &&
        SELECTEDVALUE('Date of Inspection'[date]) < CurrentDate))
RETURN 
IF(LastInspectionDate = BLANK(), BLANK(), CurrentDate - LastInspectionDate)

However, this has returned all row values as '27/12/1774', even with whole number formatting. I belive this might be from using an incorrect field in the LastInspectionDate variable. Any help would be appreciated, thank you.

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

22 Replies

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