Forum Discussion

Federaske's avatar
Federaske
Frequent Visitor
5 years ago
Solved

Self referencing calculated column (referencing previous row) - Power M help

Good evening,   hope you guys can help me! I inherited an old heavy-hard coded file with various cell references and formulas I am now trying to automate. I need one last step with my PQ query, I ...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    Hi Federaske ,

     

    Please add an index column in the query editor, and then use DAX to create the following new column:

    Column = 
var on_order= CALCULATE(SUM('Table'[ON-ORDER]),FILTER(ALLEXCEPT('Table','Table'[PRODUCT ID]),EARLIER('Table'[Index])>='Table'[Index]))
return 'Table'[ON-HAND]-on_order

     

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

V-lianl-msft's avatar
V-lianl-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Federaske ,

 

Please add an index column in the query editor, and then use DAX to create the following new column:

Column = 
var on_order= CALCULATE(SUM('Table'[ON-ORDER]),FILTER(ALLEXCEPT('Table','Table'[PRODUCT ID]),EARLIER('Table'[Index])>='Table'[Index]))
return 'Table'[ON-HAND]-on_order

 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Federaske's avatar
Federaske
Frequent Visitor
5 years ago

Hi Liang,

thank you very much! Works perfectly.

Thanks again,

Bye,

 

Federico