Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
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
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.
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.
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.
The link is the attached file.
https://drive.google.com/file/d/1D_PjVyq0KQgkMTDYqhjjEb2C2tH5S9q6/view?usp=sharing
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.
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |