Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
hi @BotBot1
try like:
Hi,
Unfortunately it didn't work, it is now returning all rows as 30/12/1899.