Search code examples
powerbidaxpowerpivot

Dax - Creating Column That Checks Dates Sequence

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?

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:

    enter image description here