How to identify the first period of missing (blank/null) data where data still exists

[LeanAccountant]

Hi,
I'm not sure whether the "subject" is worded clearly enough..
I'm trying to find the first period of missing/blank data for an employee, where data still exists for other employees - e.g. the employee has left, so we still need to report previous periods for the employee, but there will be no new data for the employee (it won't be zero, it will be blank (or perhaps null?). I'm then trying to apply conditional formatting to highlight the first missing period, but struggling because there is no data for the conditional formatting to apply itself to.

For example, we have 4 salespeople and report, say, their monthly availability (e.g. available days - usually 4*5=20 days per period). When there is a change (e.g. salesperson A in the example below), we colour the period of the change - e.g. 15 available days (due to one week's annual leave) would be coloured amber for a reduction in that period, then green for an increase back to 20 in the subsequent period - we've succeeded with this (all other periods have no colour). If a new person joins (e.g. salesperson C), we colour their first month (theoretically an "increase") blue - we've succeeded with this (all other periods have no colour). Salesperson B has had no changes, so has no colour applied (no annual leave, sick absence, etc in this example). However, when an employee leaves (e.g. employee D), their entry will reduce from 20 to zero/blank/null and the first period should be coloured red - we're struggling with this as there is no data/entry for the conditional formatting to apply itself to... we want this employee's previous entries to remain in the table, which they do, and have no colour, which they do, but want the first missing/blank/null period to be coloured red... presently the entry is left blank which we'd prefer, but if it needs to be changed to zero for this to work, please advise (as I'm relatively new to PowerBI & Dax, but quite proficient in Excel).

The searches I've tried only seem to relate to identifying where the entire data set is missing (i.e. for all employees), rather than just one series of entries.

Are you able to help, please?

[Anonymous · Accepted solution]

Hi @LeanAccountant 

The conditional formatting could not affect the blank cell in the matrix visual.

Change the blank into zero value is a workaround.

Or you can try this:
The sample table:

Here I create a measure:

MEASURE =
VAR _currentSalesperson =
    SELECTEDVALUE ( 'Table'[Salesperson] )
VAR _currentDate =
    MAX ( 'Table'[Date] )
VAR _previousdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < _currentDate )
    )
VAR _previousvalue =
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] = _previousdate
                && 'Table'[Salesperson] = _currentSalesperson
        )
    )
RETURN
    IF (
        _previousvalue <> BLANK ()
            && MAX ( 'Table'[Values] ) <> BLANK ()
            && MAX ( 'Table'[Values] ) < _previousvalue,
        "#FFC107",
        IF (
            _previousvalue <> BLANK ()
                && MAX ( 'Table'[Values] ) <> BLANK ()
                && MAX ( 'Table'[Values] ) > _previousvalue,
            "Green",
            IF (
                _previousdate <> BLANK ()
                    && _previousvalue = BLANK ()
                    && MAX ( 'Table'[Values] ) <> BLANK (),
                "Blue",
                IF ( MAX ( 'Table'[Values] ) = BLANK () && _previousvalue <> BLANK (), "Red" )
            )
        )
    )

Then add it to the conditional formatting the result:

Then add a new measure:

| = " "

And add it to the matrix, the result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saud968]

First, create a measure to identify the first missing period for each salesperson:

daxCopyFirstMissingPeriod =
VAR CurrentSalesperson = SELECTEDVALUE(Table1[Salesperson])
VAR Months = SELECTCOLUMNS(Table1, "Month", Table1[Month])
RETURN
MINX(
FILTER(
Months,
ISBLANK(LOOKUPVALUE(Table1[Value], Table1[Salesperson], CurrentSalesperson, Table1[Month], [Month]))
&& NOT(ISBLANK(MAXX(FILTER(Table1, Table1[Month] = [Month]), [Value])))
),
[Month]
)

Then, create a measure to determine if the current cell is the first missing period:

daxCopyIsFirstMissingPeriod =
VAR CurrentSalesperson = SELECTEDVALUE(Table1[Salesperson])
VAR CurrentMonth = SELECTEDVALUE(Table1[Month])
RETURN
IF(
ISBLANK(SELECTEDVALUE(Table1[Value])) &&
CurrentMonth = CALCULATE([FirstMissingPeriod], Table1[Salesperson] = CurrentSalesperson),
1,
0
)

Use this measure in your conditional formatting:

Create a new rule
Use the IsFirstMissingPeriod measure as the basis
Set it to format when the value is 1
Choose your red color

To keep the cell blank but still apply formatting:

In the table visualization, for the Value column, go to the Values formatting section
Set "Show items with no data" to On

This approach should:

Identify the first missing period for each salesperson
Apply red formatting to that cell while keeping it blank
Preserve previous data and formatting for the salesperson
Continue to show data and apply existing formatting for active employees


Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!