Average Days Between Inspections

[BotBot1]

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.

[johnt75]

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

[BotBot1]

Hi @johnt75,

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

[johnt75]

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]

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

[BotBot1]

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]

@BotBot1 

How does your data model look like?

[BotBot1]

Hi @tamerj1 ,

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

[tamerj1]

@BotBot1 

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. 

[BotBot1]

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

[tamerj1]

Sorry @BotBot1 I just noticed your reply.

I don't see the 'Date' table in the screenshot. However, please try

DaysBetweenInspectionsALL =
VAR CurrentDate =
    MAX ( 'Date'[date] )
RETURN
    MAXX (
        FILTER (
            CALCULATETABLE (
                SUMMARIZE ( 'HSE Inspection', Project[ProjectName], 'Date of Inspection'[date] ),
                ALLEXCEPT ( 'HSE Inspection', Project[ProjectName] )
            ),
            'Date of Inspection'[date] <= CurrentDate
        ),
        'Date of Inspection'[date]
    )

[BotBot1]

Hi @tamerj1,

Not a problem at all, thanks for your continued help.

My 'Date' table has no connections. 

All rows are showing as '22/02/2023'

[tamerj1]

@BotBot1 
Even for different project names?

[BotBot1]

@tamerj1 ,

Just for the new measure. This is before:

If I add 'Date of Inspection'[Date], the [date] column has dates going up each row by a day from 01/01/2000 to 31/12/2029. If I remove the [date] column, DaysBetweenInspectionsALL becomes the latest inspection dates!

[tamerj1]

@BotBot1 

Which calculated column? This is supposed to be a measure!

[BotBot1]

@tamerj1 ,

Apologies, I mean the measure!

[tamerj1]

@BotBot1 

Let me get that straight. You're not trying to get the latest inspection date of the project but only want to filter the project related rows based on the selection of the disconnected date table by keeping the rows with related inspection date before the selected 'Date'[Date]

[BotBot1]

@tamerj1,

I apologise for not being clear/causing any confusion. The 'Date' table is not of great importance and I do not have a slicer for this either.

I am simply trying to get the difference in days between each inspection date for all projects. I eventually want to get the average days between inspections based on the project, which I believe will be categorised later on in a different DAX by getting the averages of days between inspections by project.

But ideally if possible, I would just like a DAX for the average days between inspections by project in one go!

[tamerj1 · Accepted solution]

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

[BotBot1]

@tamerj1 ,

I don't suppose you could help me with the DAX for the difference between the last inspection date and today for each project in this case? Thank you

[tamerj1]

@BotBot1 

You may try

AverageDaysBtweenInspectionAndToday =
AVERAGEX (
VALUES ( Project[ProjectName] ),
INT (
TODAY ()
- CALCULATE (
MAX ( 'Date of Inspection'[date] ),
CROSSFILTER ( 'HSE Inspection'[DateHSEInspectionCreatedOnInteger], 'Date of Inspection'[DateAsInteger], BOTH )
)
)
)