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

DateJournal Number
11/05/2018JN100
11/05/2018JN200
11/05/2018JN400
12/05/2018JN100
12/05/2018JN300
12/05/2018JN400

 

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

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.

     

     

     

     

13 Replies

  • 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.

         

         

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

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

      mahoneypat 

       

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