topic Re: Average Days Between Inspections in DAX Commands and Tips

Average Days Between Inspections

Anonymous — Wed, 22 Feb 2023 14:57:26 GMT

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.

Re: Average Days Between Inspections

johnt75 — Wed, 22 Feb 2023 16:00:03 GMT

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

Re: Average Days Between Inspections

Anonymous — Wed, 22 Feb 2023 16:13:12 GMT

Hi johnt75,

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

Re: Average Days Between Inspections

johnt75 — Wed, 22 Feb 2023 16:22:23 GMT

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

Re: Average Days Between Inspections

tamerj1 — Wed, 22 Feb 2023 16:30:19 GMT

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

Re: Average Days Between Inspections

Anonymous — Wed, 22 Feb 2023 16:36:28 GMT

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.

Re: Average Days Between Inspections

tamerj1 — Wed, 22 Feb 2023 18:26:54 GMT

Anonymous

How does your data model look like?

Re: Average Days Between Inspections

Anonymous — Wed, 22 Feb 2023 20:24:02 GMT

Hi tamerj1 ,

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

Re: Average Days Between Inspections

tamerj1 — Thu, 23 Feb 2023 02:19:57 GMT

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.

Re: Average Days Between Inspections

Anonymous — Thu, 23 Feb 2023 09:56:51 GMT

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

Re: Average Days Between Inspections

tamerj1 — Thu, 23 Feb 2023 13:14:10 GMT

Sorry Anonymous 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] )

Re: Average Days Between Inspections

Anonymous — Thu, 23 Feb 2023 14:24:16 GMT

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'

Re: Average Days Between Inspections

tamerj1 — Thu, 23 Feb 2023 15:04:08 GMT

Anonymous
Even for different project names?

Re: Average Days Between Inspections

Anonymous — Thu, 23 Feb 2023 16:12:20 GMT

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!

Re: Average Days Between Inspections

tamerj1 — Thu, 23 Feb 2023 15:56:41 GMT

Anonymous

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

Re: Average Days Between Inspections

Anonymous — Thu, 23 Feb 2023 16:11:44 GMT

tamerj1 ,

Apologies, I mean the measure!

Re: Average Days Between Inspections

tamerj1 — Thu, 23 Feb 2023 16:29:48 GMT

Anonymous

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]

Re: Average Days Between Inspections

Anonymous — Thu, 23 Feb 2023 16:44:46 GMT

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!

Re: Average Days Between Inspections

tamerj1 — Thu, 23 Feb 2023 17:09:14 GMT

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

Re: Average Days Between Inspections

Anonymous — Fri, 24 Feb 2023 14:55:42 GMT

tamerj1
It worked!! Amazing. Thank you so much for your help.