Forum Discussion

Sdhn420's avatar
Sdhn420
Helper IV
5 years ago
Solved

Help with DAX

Hi, I need help to achieve the below - 

 

if value is < 0 then add this value to next month. This continues for all the months.

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, Sdhn420 

    According to your description and sample Excel file, I guess that you want to make the value of [Available Expiry] display the sum of this month’s value and last month’s value when the last month’s value is <0, right?

     

    Here’s my steps to achiev this, you can take a look and find if it’s useful:

    1. I do some transformation in the Power query and get the table like this:

     

    1. I created this calculated column:
    Available Expiry1 =

var _lastvalue=

CALCULATE(MAX([Available Expiry]),FILTER('Table',[Month]=EARLIER([Month])-1))

return

IF(_lastvalue<0,[Available Expiry]+_lastvalue,[Available Expiry])

    And I guess what you want, like this:

     

    You can download my test pbix file here

     

    If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

    How to Get Your Question Answered Quickly 

     

    Best Regards,

    Community Support Team _Robert Qin

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

6 Replies