Forum Discussion
Average Days Between Inspections
Anonymous
Please tryAverageDaysBetweenInspections = 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 ) ) )
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]
)
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
It worked!! Amazing. Thank you so much for your help.tamerj1 ,
Amazing, thank you so much
Anonymous
Please tryAverageDaysBetweenInspections = 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 ) ) )Anonymous
Even for different project names?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!
Anonymous
Which calculated column? This is supposed to be a measure!
tamerj1 ,
Apologies, I mean the measure!
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]
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 ,
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
Anonymous
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 )
)
)
)