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

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