I have im my model table that contains data from reports based on monthly reporting of employees with column names "ReportDate" and enployye numbers. I want to check that there is no gaps between the monthly dates to each employee with DAX.
For example:
EmpNum | ReportDate | CheckColumn
111 | 30.08.2019
111 | 30.09.2019
111 | 31.10.2019
222 | 30.08.2019
222| 31.10.2019 ----------> Here I want alert in my CheckColumn
Can someone find me a solution?
First you need to create a index column. Go to Edit Queries > Add Column > Index Column, starting with 1 for example.
Next you add a column with DAX which has a shift of 1 to the original column with this expression (make sure this column is from the same date format as your original column; Modelling > Format):
ShiftColumn = DATEVALUE(CALCULATE(MAX('Table'[Report Date]);FILTER('Table';'Table'[Index]=EARLIER('Table'[Index])-1)))
Next add the column with the check:
Column 2 = IF(DATEADD('Table'[Report Date].[Date];-1;DAY) = 'Table'[ShiftColumn]; TRUE(); FALSE())
The result:
