Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

missing sequential number by date

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...
Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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.

     

     

     

     

sturlaws's avatar
sturlaws
Resident Rockstar
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    sturlaws 

     

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

    • sturlaws's avatar
      sturlaws
      Resident Rockstar
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.