missing sequential number by date

[Anonymous]

Hi Newbie here

I have the following data:

Journal Table

Date	Journal Number
11/05/2018	JN100
11/05/2018	JN200
11/05/2018	JN400
12/05/2018	JN100
12/05/2018	JN300
12/05/2018	JN400

As you can see JN300 is missing on 11/05/2018 and JN 200 is missing from 12/05/2018

How would I be be able to identify these gaps in power bi, I used the following in a new column:

IF((LOOKUPVALUE('Journal Table'[Journal Number],'Journal Table'[Journal Number],'Journal Table'[Journal Number]'+1)),"N/A","Journal Reference Number Gap")

There is no reference to the date in the above.

It does highlite the gaps at 11/05/2018 - JN300 and 12/05/2018 - JN200 but also highlites the change in date as a gap (11/05/2018 - JN400 > 12/05/2018 - JN100

Thanks in advance

[mahoneypat]

Here is a measure expression you can try in a table visual with Journal Number column.

Count Missing Dates =
COUNTROWS ( EXCEPT ( VALUES ( Journal[Date] ), ALL ( Journal[Date] ) ) )

If you want the list of missing dates, you could rpelace COUNTROWS() with CONCATENATEX().  To make the column version of the above, use CALCULATETABLE(VALUES(Journal[Date]), ALLEXCEPT(Journal[JournalNumber])) instead of VALUES (Journal[Date]).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat 

Thanks, the date is not the issue, I am trying to identify the missing journal numbers only. 

[sturlaws]

Hi @Anonymous 

the sample data does not quite fit the code of your calculated column, where you are using journal number + 1 to detect gaps. 

If your data resembles the sample data, I would create a new table like this:

Journals2 = GENERATEALL(VALUES(Journals[Journal Number]),VALUES(Journals[Date]))

and then create a new column like this:

IsMissingFlag =
IF (
    ISBLANK (
        LOOKUPVALUE (
            Journals[Journal Number],
            Journals[Journal Number], Journals2[Journal Number],
            Journals[Date], Journals2[Date]
        )
    ),
    1,
    BLANK ()
)

 Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

@sturlaws 

Thanks, the date is not the issue, I am trying to identify the missing journal numbers only. 

[sturlaws]

With the solution I suggested, you can set up a table visual and filter for isMissingFlag=1.

Is the sample data you have provided accurate? In the dax code you posted, you write 'Journal Table'[Journal Number]'+1 which makes no sense when the the journal number contains characters.

[Anonymous]

@sturlaws 

Apologies for all this, I tried to simplify the data alot. Removed alot of info but tried to leave only what I need, ie the journal number and date (as the date "resets" the journal number and it starts at 100 again).

I split the characters out from the journal numbers so I ended up with 1 column with JN and the other with the numeric info - 100, 200, 300.

I wanted to add a column indicating the Journal Number gap, as I find with the measure, when you view the report and want to "see records" it does not allow it.

[Anonymous]

I think let me rather include the info as is:

Journals

Entry passed date	Transaction Reference Number	Journal Gap Analysis	Transaction Reference Number - Copy 1	Transaction Reference Number - Copy.2
11/05/2018	JN001	N/A	JN	001
11/05/2018	JN002	N/A	JN	002
11/05/2018	JN003	Journal Reference Gap	JN	003
11/05/2018	JN005	N/A	JN	005
11/05/2018	JN006	Journal Reference Gap
12/05/2018	JN001	N/A	JN	001
112/05/2018	JN002	N/A	JN	002
12/05/2018	JN003	N/A	JN	003
12/05/2018	JN004	Journal Reference Gap	JN	004
12/05/2018	JN006	N/A	JN	006

What I used to identify the gap:

IF((LOOKUPVALUE('Journals'[Transaction Reference Number - Copy.2],'Journals'[Transaction Reference Number - Copy.2],'Journals'[Transaction Reference Number - Copy.2]+1)),"N/A","Journal Reference Number Gap")

So it picks up a gap at date change as well 11/05/18 (JN006 > 12/05/18 (JN001).

So I need to identify sequential gaps in hte journal numbers by date, hope this makes more sense.

[sturlaws · Accepted solution]

it makes more sense 🙂

With the code you are using, if you want to avoid having the date change marked as a gap, you need a way to determine the last journal number. And I imagine that the number of journals will be increasing, so you will have to determine the number of journals per date. 

I know it contains date, but this is how I would do it.

Column =
VAR _currentDate =
    CALCULATE ( SELECTEDVALUE ( journals3[Entry passed date] ) )
VAR _currentTransaction =
    CALCULATE ( SELECTEDVALUE ( journals3[Transaction Reference Number - Copy.2] ) )
VAR _maxTransaction =
    CALCULATE (
        MAX ( journals3[Transaction Reference Number - Copy.2] ),
        FILTER ( ALL ( journals3 ), journals3[Entry passed date] = _currentDate )
    )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( journals3 ),
                _currentDate = journals3[Entry passed date]
                    && _currentTransaction + 1 = journals3[Transaction Reference Number - Copy.2]
            )
        ) = 0
            && _currentTransaction < _maxTransaction,
        "Journal Reference Gap",
        "N/A"
    )

There is 1 other option. If you are absolutely sure the rows is loaded in the correct order, you can set an index column in Power Query, and use that to check if the row with the next index number is the row where journal number is 001. 

But either way, you will have a potential issue with what if the last journal is missing.

[Anonymous]

@sturlaws 

Thanks for the help so far, quick question, for column =

VAR _maxTransaction =
    CALCULATE (
        MAX ( journals3[Transaction Reference Number - Copy.2] ),
        FILTER ( ALL ( journals3 ), journals3[Entry passed date] = _currentDate )
    )

I get the total count of the distinct values in the Journals[Transaction Reference Number - Copy.2] column, is that correct? 

[sturlaws]

The filter is set to filter on _currentDate, so it will be the maximum journal number that day. If you remove this part 

 journals3[Entry passed date] = _currentDate

you will get the maximum journal number across all dates. 

In some sense you can interprete _maxTransaction as the total count of the distinct values in the Journals[Transaction Reference Number - Copy.2] column, in that if there were no missing journal numbers, and 1 is the first journal number, then _maxTransaction=distinctcount(journal number)

[Anonymous]

@sturlaws 

Hope this is the last question from me regarding this

See below, using statments provided it seems it now identifies every journal number less than the max journal number for the day as a "gap":

Journal Gap Analysis

IF(COUNTROWS(FILTER(ALL('Journals'),'Journals'[VAR_currentDate].[Date]='Journals'[Entry Passed Date - Copy].[Date]&&'Journals'[VAR_currentTransaction]+1='Journals'[Transaction Reference Number.2]))=0&&'Journals'[VAR_currentTransaction]<'Journals'[VAR_maxTransaction],"Gap","n/a")

[sturlaws]

not sure what you are doing here. Have a look at the attached file

[Anonymous]

@sturlaws 

Thanks so much, its working now, appreciate all the help.

Like I said Power BI Newbie/Noob here 🙈 , dont know what I was doing earlier🤔