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

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.

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