Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

retrieve column value from current iterated row

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

 

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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

     

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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