Forum Discussion

TrentS's avatar
TrentS
Icon for Helper IV rankHelper IV
2 years ago
Solved

Best approach for Date sequence check

Good day to all,

 

I have about 20 events/dates in a shipping sequence that I need to run a check on.
I need to determine two things:

1. Any missing dates in the sequence (think missing scans during the process)

2. If any dates are out of sequence.

Using the screenshot above.
1. I would need a count of the two rows missing dates. (Lets just call it per row at this stage, so 2 in this case.)
2. In order 105 (yellow) obviously we cannot pack it after shipping so I need to capture that.

Ultimately I would like to produce those rows in an output visual table as well as a count.


I have a secondary table that ranks the sequence to use as a reference. (Static/Imported)
Given the high number of events, nested IFs and Switch do not seem to be elegant solutions as I have seen in my search of this forum.

Suggestions on how to approach this?
I appreciate your time!

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    TrentS 

    you can create two measures

    Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table', ISBLANK('Table'[order processed])||ISBLANK('Table'[packed])))
    Measure 2 = CALCULATE(COUNTROWS('Table'),FILTER('Table', 'Table'[packed]>'Table'[shipping]))
     
    or create columns
    missingdate = if(ISBLANK('Table'[order processed])||ISBLANK('Table'[packed]),1,0)
    wrongdate = if('Table'[packed]>'Table'[shipping],1,0)
     
    then you sum the column to get the total number
     
    pls see the attachment below
     
     

     

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    TrentS 

    you can create two measures

    Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table', ISBLANK('Table'[order processed])||ISBLANK('Table'[packed])))
    Measure 2 = CALCULATE(COUNTROWS('Table'),FILTER('Table', 'Table'[packed]>'Table'[shipping]))
     
    or create columns
    missingdate = if(ISBLANK('Table'[order processed])||ISBLANK('Table'[packed]),1,0)
    wrongdate = if('Table'[packed]>'Table'[shipping],1,0)
     
    then you sum the column to get the total number
     
    pls see the attachment below
     
     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Write these calculated column formulas

    Missing dates = 1*(Data[Order placed]=BLANK()||Data[order processed]=BLANK()||Data[packed]=BLANK()||Data[shipped]=BLANK())
    Dates ordered properly = 1*(if(Data[Missing dates]=1,BLANK(),not(Data[order processed]>=Data[Order placed]&&Data[packed]>=Data[order processed]&&Data[shipped]>=Data[packed])))

    Hope this helps.

     

  • TrentS's avatar
    TrentS
    Icon for Helper IV rankHelper IV
    2 years ago

    Thank you Ashish and Ryan. Kudos given.
    I am going to leave it as unresolved for a bit as I worry about upscaling that to the 20+ dates that would have to be used in those measures/columns. This data set already has a considerable number of custom columns added and I worry about adding even more.
    Any thoughts on an approach to that many steps needing the comparisons? (I only placed 4 in the example but there are considerably more in the actual data set.)