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

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

  • 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