How to use DAX to create column that shows dynamic results

[Syruswan]

Hello Power BI fans,

I've been stuck on this problem for a while and hopefully can find a solution here.

Below is the scenario.

I have a sales table which includes my monthly achieved revenue. Every start of the month, I would like to calculate the new monthly goal based on achieved revenue from last month. If the achieved revenue from last month had a surplus, then the monthly goal for the rest of the months remain the same, if there's a deficit, then I need to add those deficit evenly to the rest of the months in the year. The second picture can help to understand the scenario.

The red circle in the first picture is not showing what I want cause it seems like its calculated based on each row.

Below is my dax:

Column =

VAR _n = MONTH('Goal'[StartofMonth].[Date])

VAR _Looptable = GENERATESERIES(1,_n)

VAR _NewMonthlyGoal = 'Goal'[Monthly Goal] + IF('Goal'[Achieved.Achieved Monthly Revenue] < 'Goal'[Monthly Goal], ('Goal'[Monthly Goal] - 'Goal'[Achieved.Achieved Monthly Revenue])/(12-_n),0)

VAR _Looptable1 = ADDCOLUMNS(_Looptable,"_NMG", 'Goal'[Monthly Goal] + IF('Goal'[Achieved.Achieved Monthly Revenue] < 'Goal'[Monthly Goal], ('Goal'[Monthly Goal] - 'Goal'[Achieved.Achieved Monthly Revenue])/(12-_n),0))

VAR _max = MAXX(_Looptable1,[Value])

Return

MAXX(FILTER(_Looptable1,[Value]=_max),[_NMG])

[v-kelly-msft]

Hi  @Syruswan ,

Try to modify your column as below:

Column = 
Var _lastmonthgoal=CALCULATE(MAX('Goal'[Achieved.Achieved Monthly Revenue]),FILTER('Goal','Goal'[StartofMonth]=MAXX(FILTER('Goal','Goal'[StartofMonth]<EARLIER('Goal'[StartofMonth])),'Goal'[StartofMonth])
Return
IF( 'Goal'[Monthly Goal]>_lastmonthgoal, 'Goal'[Monthly Goal]+DIVIDE('Goal'[Monthly Goal]-_lastmonthgoal,12-MONTH('Goal'[StartofMonth)+1),'Goal'[Monthly Goal])

If the above measure doesnt work,pls upload your .pbix file to onedrive business and share the link with me.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Syruswan]

Hi Kelly, 

Thanks for the help. I tried the measure but it didnt work.

Below is the file.

https://drive.google.com/file/d/10Prv_K3yeun1WovMp378SuI2sHHHT97D/view?usp=sharing

[Ashish_Mathur]

Hi,

Could you share the MS Excel file with your Excel formula so that i can understand your logic?  Share the link from where i can download your MS Excel file.

[v-kelly-msft]

Hi  @Syruswan ,

Create 2 columns as below:

Gap = 
var _lastmonth=MAXX(FILTER('Goal','Goal'[StartofMonth]<EARLIER('Goal'[StartofMonth])),'Goal'[StartofMonth])
Var _lastmonthgoal=CALCULATE(MAX('Goal'[Achieved.Achieved Monthly Revenue]),FILTER(ALL('Goal'),'Goal'[StartofMonth]=_lastmonth))
var _diff='Goal'[Monthly Goal]-_lastmonthgoal
Return
IF(_diff<0||_lastmonthgoal=BLANK(),0,DIVIDE(ABS(_diff),13-MONTH('Goal'[StartofMonth])))

_New monthly goal = 
var _previousgap=CALCULATE(MAX('Goal'[Gap]),FILTER('Goal','Goal'[StartofMonth]<=EARLIER('Goal'[StartofMonth])))
Return
IF(_previousgap=0,'Goal'[Monthly Goal],'Goal'[Monthly Goal]+SUMX(FILTER(ALL('Goal'),'Goal'[StartofMonth]<=EARLIER('Goal'[StartofMonth])),'Goal'[Gap]))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Syruswan]

Hi Kelly,

This is the solution I'm looking for. Thank you so much for the help!!

However, in the real case, I have several salesman. I just added another column call "salesman". I want to have the calculation done on each salesman, in other words, the calculation should work more like independant sub table with 12 rows (12 months). How can I tweak the DAX for the calculation? I tried using FILTER to come out with a sub table first. Then still dont work. 

Gap =

VAR _Salesperson = FILTER('Goal', 'Goal'[Salesman] = EARLIER('Goal'[Salesman] ))

var _lastmonth=MAXX(FILTER(_Salesperson,'Goal'[StartofMonth] < EARLIER('Goal'[StartofMonth])),'Goal'[StartofMonth])

Var _lastmonthgoal=CALCULATE(MAX('Goal'[Achieved.Achieved Monthly Revenue]),FILTER(_Salesperson,'Goal'[StartofMonth]=_lastmonth))

var _diff='Goal'[Monthly Goal]-_lastmonthgoal

Return

IF(_diff<0||_lastmonthgoal=BLANK(),0,DIVIDE(ABS(_diff),13-MONTH('Goal'[StartofMonth])))

The link is the attached file.

https://drive.google.com/file/d/1D_PjVyq0KQgkMTDYqhjjEb2C2tH5S9q6/view?usp=sharing

[v-kelly-msft]

Hi @Syruswan ,

Modify the columns as below:

Gap = 
VAR _Salesperson = FILTER('Goal', 'Goal'[Salesman] = EARLIER('Goal'[Salesman] ))
var _lastmonth=MAXX(FILTER(_Salesperson,'Goal'[StartofMonth]<EARLIER('Goal'[StartofMonth])),'Goal'[StartofMonth])
Var _lastmonthgoal=CALCULATE(MAX('Goal'[Achieved.Achieved Monthly Revenue]),FILTER(_Salesperson,'Goal'[StartofMonth]=_lastmonth))
var _diff='Goal'[Monthly Goal]-_lastmonthgoal
Return
IF(_diff<0||_lastmonthgoal=BLANK(),0,DIVIDE(ABS(_diff),13-MONTH('Goal'[StartofMonth])))

_New monthly goal = 
VAR _Salesperson = FILTER('Goal', 'Goal'[Salesman] = EARLIER('Goal'[Salesman] ))
var _previousgap=CALCULATE(MAX('Goal'[Gap]),FILTER(_Salesperson,'Goal'[StartofMonth]<=EARLIER('Goal'[StartofMonth])))
Return
IF(_previousgap=0,'Goal'[Monthly Goal],'Goal'[Monthly Goal]+SUMX(FILTER(_Salesperson,'Goal'[StartofMonth]<=EARLIER('Goal'[StartofMonth])),'Goal'[Gap]))

Finally you will see:

For the modified .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Syruswan]

Hello Kelly, 

Thanks for the help. 

I'm looking at June and the number is a bit off. I'm expecting the new monthly goal in June should be (($85,983.45 - $81,532.9) / 7 ) + $85983.45 = $86,619.24. I wanna calculated the difference between the monthly achieved goal and the new calculated monthly goal.

I figure out it might be cause by 

var _diff= 'Goal'[Montly Goal] -_lastmonthgoal

So I changed it to below but seems not work at all.

var _diff= 'Goal'[Achieved.Achieved Monthly Revenue] -_lastmonthgoal

[v-kelly-msft]

Hi @Syruswan ，

Yes,as in my calculation, the _diff is based on 83333.33,not on the dynamic new value,I have bothered by the issue for 2 days,but still couldnt find a solution,as you need to calculate based on dynamic result,it would be difficult.Or in another word,it may cause multiple columns to be the middle results to get a final column.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Syruswan]

Hi Kelly,

Thank you so much for the help. Yes, this dynamic calculation is really hard. I found an interesting post which is quite similar to what you mentioned that adding a lot of columns might be worth trying. The reason is we are doing the calculation based on a new value from last row. In order to find a way to either store or create that value, ADDCOLUMNS might be something worth trying. I've been stuck on this for a long time and haven't figured out a way. Below is just a post I found.