topic Get the previous value calculated with ALLEXCEPT in DAX Commands and Tips

Get the previous value calculated with ALLEXCEPT

mtrevisiol — Wed, 06 Oct 2021 10:25:25 GMT

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

Re: Get the previous value calculated with ALLEXCEPT

HotChilli — Thu, 07 Oct 2021 01:47:29 GMT

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

Re: Get the previous value calculated with ALLEXCEPT

mtrevisiol — Thu, 07 Oct 2021 06:44:12 GMT

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)