Forum Discussion

mtrevisiol's avatar
mtrevisiol
Icon for Helper V rankHelper V
5 years ago
Solved

Get the previous value calculated with ALLEXCEPT

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 pro...
  • HotChilli's avatar
    HotChilli
    5 years ago

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

     

HotChilli's avatar
HotChilli
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
mtrevisiol
Icon for Helper V rankHelper V
5 years ago

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)