retrieve column value from current iterated row

[handrade]

Hello all,

I would like to create a measure that sums a column based on a filter I apply. The query gets complex beacuse I need to retrieve the column value from the current iterated row. My context is the following:

Production table:

Shipping Table:

Expected output from Forecast measure:

 

So far this is the code I've come up with, but like mentioned above I haven't found a way to retrieve the current column value from the iterated row. I might be thinking this the incorrect way. If there is another way that you can think for calculating this measure I will appreciate if you share your thoughts.

Forecast = 
    SUM('Production'[Production Quantity]) 
    - 
    SUMX('Shipping', 
          FILTER('Shipping', 
             'Shipping'[Requested Ship Date] <= **Column value being iterated by row in SUMX**
          )
    ) 

[v-yuta-msft · Accepted solution]

Hi handrade,

Please follow steps below:

1. Click Edit Queries-> Merge Queries as New to merge 'Production' and 'Shipping' into one table based off Part Number.

 

2. Click Add Column-> Index Column to create a index column so that the iteration can be implemented by Index Column.

3. After Close&Apply, create a calculate column running total to achieve the cummulative total of Requested Quantity, then you can achieve the Forecast.

Running Total = 
CALCULATE (
    SUM ( Merge1[Requested Quantity] ),
    FILTER (
        Merge1,
        Merge1[Index] <= EARLIER ( Merge1[Index] )
            && Merge1[Part Number] = EARLIER ( Merge1[Part Number] )
    )
)

Forecast = MAX(Merge1[Production Quantity]) - MAX(Merge1[Running Total])

 

Regards,

Jimmy Tao