Forum Discussion

PBE's avatar
PBE
Helper II
3 years ago
Solved

Use Previous Value In Column In Calculation

Hi, I don't know how to do this in Power BI. Any ideas on how to do this in DAX or Power Query.   I found this not easy to explain so any questions please let me know.   Thanks   Pete   The ...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi PBE ,

     

    Please try:

    Excess = SUMX(FILTER('Table',[Row]<=EARLIER('Table'[Row])),[Amount]-[Available])

    Final output:

    Best Regards,

    Jianbo Li

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

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi PBE ,

     

    Please try:

    Measure = 
var _a = ALLSELECTED('Table'[Work Type])
var _b = MAX('Table'[Row])
return SUMX(FILTER(ALL('Table'),[Work Type] in _a&&[Row]<=_b),'Table'[Amount]-'Table'[Available])

    Output:

    Best Regards,

    Jianbo Li

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

PBE's avatar
PBE
Helper II
3 years ago

Hi Jianbo Li

 

Thank you very much it works well.

I wonder if you could help with one last thing.

I need an extra column which highlights where Amount - Available is a positive number. Please see explanation below. I hope it makes sense!

Thanks

 

Pete

v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

Hi PBE ,

 

Sorry, this question is beyond the topic at the beginning of the post.

In order to make the post more relevant, please consider posting a new thread for this issue so that more users can participate and also better help other users with similar problems.

Thank you for your understanding!🙂

 

Best Regards,

Jianbo Li

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

  • PBE's avatar
    PBE
    Helper II
    3 years ago

    Thanks for your help