Forum Discussion

BudMan512's avatar
BudMan512
Icon for Helper V rankHelper V
3 years ago

Missing Tickets

Hey, I am having trouble figuring out what appears to be a fairly simple report.  Any help would be appreciated. I have a Sales table containing 3 different batches of delivery tickets.  The Ticket...
vicky_'s avatar
vicky_
Icon for Super User rankSuper User
3 years ago

Here's what i've got for the dax:

Diff = 
var previousTicket = CALCULATE(MAX('Table'[Ticket]), ALL('Table'[Ticket]), 'Table'[Ticket] < SELECTEDVALUE('Table'[Ticket]))
var previous = IF(ISBLANK(previousTicket), SELECTEDVALUE('Table'[Ticket]), previousTicket) 
return CALCULATE(SUM('Table'[Ticket]) - previous)

 

  • BudMan512's avatar
    BudMan512
    Icon for Helper V rankHelper V
    3 years ago

    Hi Vicky, 

    Here is a screenshot of my real world data.  I have substituted 'History'[HIST_REF_NO] for 'Table'[Ticket] in your script.  As you can see, the calculation starts out fine but soon goes off the rails. I wonder if you see what the issue might be. Thanks for your help.

    Bud

     

  • BudMan512's avatar
    BudMan512
    Icon for Helper V rankHelper V
    2 years ago

    vicky_ 

    Greg_Deckler 

    Here is a link to the report I need help with. It is in OneDrive.

    https://ruralcomputers-my.sharepoint.com/:f:/g/personal/bingraham_rccbi_com/Epxdh6HJSABKtqL8mDdM2sYB2w7lXZg5VUSKt08M8Q-FPg?e=Gag6SM

    Below is a screenshot of the report.  It is intended to identify missing invoices.  The first invoice in a Batch should be a zero in the 'Diff' column and subseqent invoices should be the difference between an Invoice number and the previous Invoice number.  Normally the difference should be 1 unless there are missing invoices. You can see in the below screenshot the first two batches are working perfectly and then it breaksdown.  The Measure's DAX is below.

    Diff =
    var previousTicket = CALCULATE(MAX('History'[Invoice]), ALL('History'[Invoice]), 'History'[Invoice] < SELECTEDVALUE('History'[Invoice]))
    var previous = IF(ISBLANK(previousTicket), SELECTEDVALUE('History'[Invoice]), previousTicket)
    return CALCULATE(SUM('History'[Invoice]) - previous)
     
    I need the report to calculate the difference between invoices accurately while making the diff for the first invoice of a new batch be zero.  If anyone could help me with this, it would be greatly appreciated.