Manual "forecasting” with DAX

[dw700d]

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

[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.

[v-xicai]

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.

[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.⚡

[amitchandak]

@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

[dw700d]

@amitchandakthank 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