Forum Discussion
Post PatronManual "forecasting” with DAX
I have a set amount of money that must be spent in 31 months. The begining date was 12/30/2019 and the end date is 7/30/22. As each month passes I would like to create a dynamic tracker that tells me how much I need to spend each month to meet my goal. The goal is to spend $1,000,000 in 31 months. No money has been spent yet but money will be spent begining this month
For instance if July EOM Spend = $2,000, my tracker should calculate the remaining months left which is 24 months. It should then calculate how much I need to spend over the next 24 months to meet my goal (998,000/24)=41583
Then if August EOM spend = $10,000, my tracker should calculate the remaining months left which is 23 months. It should then calculate how much I need to spend over the next 23 months to meet my goal (988,000/23) =42956.
The tracker would update at the end of each month
Any thoughts on how I can accomplish this using DAX?
Regards
6 Replies
- amitchandak
Super User
dw700d , if you have fix number between two dates, you can allocate it monthly or daily and use that.
https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0
dw700damitchandakthank you but I am still a little confused. Where can i find the tracker? I see the monthly and daily target but I dont see the calculation that helps me understand how much I need to spend each month as the months progress. I would like to include the tracker in a line and stacked column chart like the one below. I would like the tracker to be the line so I can compare actual spend versus the calculated spend needed over the remaining months to meet my target. Thanks for your help again
- parry2k
dw700d hope the attached solution helps, tweak it as per your need.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
v-xicaiCommunity Support
Hi dw700d ,
You may create a calendar table using DAX below, then create relationship with your fact data table on date field.
Calendar= CALENDAR(DATE(2019,12,30),DATE(2022,7,30))Then you create measures like DAX below.
Tracker = Var _Goal= 1000000 Var _Date=SELECTEDVALUE('Calendar'[Date]) Var _DiffMonth= DATEDIFF(SELECTEDVALUE('Calendar'[Date]) ,DATE(2022,7,30), MONTH ) Return DIVIDE(_Goal -MAX(Table1[Actual Spent]), DiffMonth)Finally, you may put 'Calendar'[Date] into "Shared axis" box of Line and clustered column chart, put Table1[Tracker] into "Line values" box, and put Table1[Actual Spent] into "Column values".
Or could you share some data sample and expected output? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
How to Get Your Question Answered Quickly
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- dw700d
thank you all for your input, I was able to figure this out
- v-xicai
Hi dw700d ,
Happy to hear that you have resolved the problem by yourself. Would you please share your solution and then mark it as an answer, so that it can help others having similar issue?
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
