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.

     

     

     

     

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I think let me rather include the info as is:

Journals

Entry passed dateTransaction Reference NumberJournal Gap AnalysisTransaction Reference Number - Copy 1Transaction Reference Number - Copy.2
11/05/2018JN001N/AJN

001

11/05/2018JN002N/AJN002
11/05/2018JN003Journal Reference GapJN003
11/05/2018JN005N/AJN005
11/05/2018JN006Journal Reference Gap  
12/05/2018JN001N/AJN001
112/05/2018JN002N/AJN002
12/05/2018JN003N/AJN003
12/05/2018JN004Journal Reference GapJN004
12/05/2018JN006N/AJN006

 

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's avatar
    sturlaws
    Resident Rockstar
    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.

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        sturlaws
        Resident Rockstar
        6 years ago

        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)