Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Last and average between dates

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 o...
FreemanZ's avatar
FreemanZ
Super User
3 years ago

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
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi,

     

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