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