Forum Discussion
missing sequential number by date
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.
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.
Thanks, the date is not the issue, I am trying to identify the missing journal numbers only.
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.
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.
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.