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)