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

     

2 Replies

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