Forecast the cost for rest of the days/month based on todays cost

[Mohivaj]

Hi,

I have a requirement  to forecast the cost for the next day/month based on todays cost.

[Mohivaj]

Date	Cost
5/19/2024	31000
5/20/2024	32000
5/21/2024
5/22/2024
5/23/2024
5/24/2024
5/25/2024
5/26/2024
5/27/2024
5/28/2024
5/29/2024
5/30/2024
5/31/2024
6/1/2024
6/2/2024
6/3/2024
6/4/2024
6/5/2024
6/6/2024
6/7/2024
6/8/2024
6/9/2024
6/10/2024
6/11/2024

[Anonymous]

Hi,@Mohivaj 

Thank you for sharing your question with us.
I have carefully studied the information you have provided, but I must be honest and tell you that I do not understand some parts deeply enough, especially the logic of prediction as you would like it to be. Could you please provide a more detailed explanation or relevant examples?

Regarding the issue you raised, my solution is as follows:
My forecasting logic is to take the values that are known now and return them to the next day of the month on the same date for cost forecasting.

1. Below are the Calculated Columns I've created for your needs:

fore = 
VAR cm=MONTH('foresee table'[Date])
VAR cd=DAY('foresee table'[Date])
var nextm=CALCULATE(MAX('foresee table'[Cost]),FILTER('foresee table',DAY('foresee table'[Date])=cd-1))
RETURN nextm

2.Here's my final result, which I hope meets your requirements.

I look forward to hearing from you and thank you for your understanding and support.

Best Regards,

Leroy Lu

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

[Mohivaj]

Hi @Anonymous ,

Getting syntax error as  it is not able to find the month and store in the variable . The date column is not visible while defining the variable.

[Anonymous]

Hi,@Mohivaj 

Thank you very much for your response.

Could you give us more details?

Are you using measure or calculation columns? The code I provided before is calculated column, the following is my updated calculation column:

fore = 
VAR cd=DAY('foresee table'[Date])
var nextm=CALCULATE(MAX('foresee table'[Cost]),FILTER('foresee table',DAY('foresee table'[Date])=cd-1))
RETURN nextm

If there is a problem with your metric or calculation column, please provide your requirements and your code.
Here is the relevant documentation:
Tutorial: Create calculated columns in Power BI Desktop - Power BI | Microsoft Learn

Tutorial: Create your own measures in Power BI Desktop - Power BI | Microsoft Learn
Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu