Last and average between dates

Anonymous — Fri, 17 Feb 2023 17:48:10 GMT

Hi,

I am trying to replicate the following table in Power BI but am struggling with writing the DAX for calculating some of the columns.

I cannot seem to get the last formal inspection date or the two last columns to work as when I change the format to latest date for the last inspection date column it says all of the last inspection dates for all projects were 31/12/2029.

Here is what it currently looks like, with the 'Last Formal Inspection Date' currently showing all inspection dates.

I have the following attempted DAX with the variable names for getting the days between inspections to try and get the last two average columns from the original Excel screenshot:

AvgDaysBetweenFormalInspections = VAR CurrentProject = ALL(Project[ProjectName]) VAR CurrentDate = ALL('Date of Inspection'[date]) VAR LastInspection = CALCULATE( MAX('Date of Inspection'[date]), FILTER( ALL(Project), Project[ProjectName] = CurrentProject && ALL('Date of Inspection'[date]) < CurrentDate ) ) RETURN IF (LastInspection = BLANK(), BLANK(), CurrentDate - LastInspection)

This currently doesn't work as it says 'a table of multiple values was supplied where a single value was expected'.

I'm not sure if this is because I am live connected or that it is because I am using 'ALL', but if anyone can assist me on any of the 3 columns it would be hugely appreciated.

Thank you.

Re: Last and average between dates

FreemanZ — Sat, 18 Feb 2023 06:32:37 GMT

hi Anonymous

try like:

AvgDaysBetweenFormalInspections =

VAR CurrentProject =

MAX(Project[ProjectName])

VAR CurrentDate =

MAX('Date of Inspection'[date])

VAR LastInspection =

CALCULATE(

MAX('Date of Inspection'[date]),

FILTER(

ALL(Project),

Project[ProjectName] = CurrentProject &&

'Date of Inspection'[date]< CurrentDate

)

RETURN

IF(

LastInspection = BLANK(),

BLANK(),

CurrentDate - LastInspection

)

Re: Last and average between dates

Anonymous — Mon, 20 Feb 2023 09:02:48 GMT

Hi,

Unfortunately it didn't work, it is now returning all rows as 30/12/1899.

topic Last and average between dates in DAX Commands and Tips

Last and average between dates

Re: Last and average between dates

Re: Last and average between dates