Average Days Between Inspections
Hi, I am trying to get the average days between inspection dates. Below is a breakdown of current dates by project (with blue and green dots being 2 different projects). Below is my attempted DAX for getting the days between inspections, with an average calculation for averages later on: DaysBetweenInspectionsALL = VAR CurrentProject = SELECTEDVALUE(Project[ProjectName]) VAR CurrentDate = SELECTEDVALUE('Date of Inspection'[date]) VAR LastInspectionDate = CALCULATE( MAX('Date'[Date]), FILTER( ALL(Project), Project[ProjectName] = CurrentProject && SELECTEDVALUE('Date of Inspection'[date]) < CurrentDate)) RETURN IF(LastInspectionDate = BLANK(), BLANK(), CurrentDate - LastInspectionDate) However, this has returned all row values as '27/12/1774', even with whole number formatting. I belive this might be from using an incorrect field in the LastInspectionDate variable. Any help would be appreciated, thank you.
Convert decimal number to time
So I am importing data from Access and in the power query editor I have tried transform to time, but i get "ERROR". I have 2 times One column has time in format such as 71500 or 151500 and other column has 715 or 1214. In the first case, the time has seconds, which I don't need as all are 00. It does not show 0 in the beginning, so it goes 8 AM being 800 and 3PM being 1500 etc. In the second case, seconds do not exist, and again 0 does not exist. In addition, should I be fixing/labeling data in Access or simply do it in Access? How does it work on the load? I also skip Excel and work directly with the file in PBI. I frequently run out on 32 bit BI, and i can't use 64bit BI since our server with data is 32bit.
