Get the previous value calculated with ALLEXCEPT

[mtrevisiol]

Hi everyone. I've got this table:

• ProdID is the key: it represents the production order
• Date represents the date. In this case from 28th to 30 september
• Product is the name of the product
• Total Qty is the total number of pieces that must be produced in the current production order
• Produced Pieces is the quantity produce. It represents to which point we are with the production: it is a value that is increased as pieces are produced
• MaxProducedPiecesToday represents the total quantity produced in each date, and it is calculated as the maximum of ProducedPieces for each date, using ALLEXCEPT, as explained in the picture

My purpose is to calculate the number of Pieces Produced Today, given by the maximum of pieces produced today minus the maximum of pieces produced in the previous date, that might not be the day immediately preceding.

Can you help me? Thanks in advance. Here is the PBIX file: https://www.dropbox.com/s/2h9avwap7xy2qw1/Production%20order.pbix?dl=0

[HotChilli · Accepted solution]

This should get you the value from the previous date.  You can then work out the subtraction. Please test with more products and different date setups.

NewColumn = VAR _prodID = Query[ProdID]
VAR _date = Query[Date]
VAR _fromDate = CALCULATE(MAX(Query[Date]), FILTER(Query, Query[Date] < _date  && Query[ProdID] = _prodID))

RETURN
CALCULATE(MAX(Query[MaxProducedPcsToday]), FILTER(Query, Query[Date] = _fromDate && Query[ProdID] = _prodID))

[mtrevisiol]

Thanks for help @HotChilli 

I did it by creating this column:

Max precedente =

var max_precedente = CALCULATE (

   MAX ( Query[ProducedPcs] ),

   FILTER (

      ALL ( Query ),

      Query[Date] < EARLIER ( Query[Date] ) && Query[MOId] = EARLIER(Query[ProdID])

      )

)

RETURN IF(max_precedente>0, max_precedente, 0)