Last and average between dates

[BotBot1]

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.

[FreemanZ]

hi @BotBot1 

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

)

[BotBot1]

Hi,

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