Calculating a Forecast value

[FormworkFan]

I have a Dim Table of Sorts for 2025 revenue goals. This table has 3 columns (Division, Sales Region & 2025 Goal).

I'm using this to compare my YTD Sales with Total Goals for 2025.  I would to now set a monthly or evenly daily Goal Target.

I have a 2nd Dim Table for that defines the perecentage of total goal I'm expected based on the current day of the year. ( 1 to 365).

Day 1 (Jan 1) expected percentage of goal is about 0.0%

Day 182 (Jun 30) expected perdentage of goal is about 50% 

Day 365 (31-Dec) is 100% of goal.

Please note this I'm accountly for some seasonality, so the increase each day is not the same.

I'm using my Total Sales to plot a colum chart to compare Montly Sales vs Monthly Sales from the Previous year.

I'd like to incorporate ethier a 3rd Bar for the expect Forecast number for each month or a line.  I kind of now I have to multiple by percentage figure by the Goal value, but I can't figure out the DAX to get this working.

Any comment or thougths would be helpful.

[v-bmanikante · Accepted solution]

Hello @FormworkFan ,

Could you please try the below mentioned DAX Measures:

Expected Goal (Projected) =
SUMX(
Goals,
Goals[2025 Goal] * MAX('TargetIndex'[% of Target])
)

Expected Goal (Monthly) =
VAR StartDate = DATE(YEAR(MAX('Calendar'[Date])), MONTH(MAX('Calendar'[Date])), 1)
VAR EndDate = EOMONTH(StartDate, 0)
VAR PriorEnd = EOMONTH(StartDate, -1)

VAR EndPct =
CALCULATE(
MAX('TargetIndex'[% of Target]),
FILTER(ALL('Calendar'), 'Calendar'[Date] = EndDate)
)

VAR StartPct =
CALCULATE(
MAX('TargetIndex'[% of Target]),
FILTER(ALL('Calendar'), 'Calendar'[Date] = PriorEnd)
)

RETURN
SUMX(
Goals,
Goals[2025 Goal] * (EndPct - StartPct)
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
B Manikanteswara Reddy

[v-bmanikante]

Hi @FormworkFan ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @FormworkFan ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[FormworkFan]

Sorry for the delay in my response. Things have been a bit crazy.

The solution works really well.

[v-bmanikante · Accepted solution]

Hi @FormworkFan ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante · Accepted solution]

Hello @FormworkFan ,

Could you please try the below mentioned DAX Measures:

Expected Goal (Projected) =
SUMX(
Goals,
Goals[2025 Goal] * MAX('TargetIndex'[% of Target])
)

Expected Goal (Monthly) =
VAR StartDate = DATE(YEAR(MAX('Calendar'[Date])), MONTH(MAX('Calendar'[Date])), 1)
VAR EndDate = EOMONTH(StartDate, 0)
VAR PriorEnd = EOMONTH(StartDate, -1)

VAR EndPct =
CALCULATE(
MAX('TargetIndex'[% of Target]),
FILTER(ALL('Calendar'), 'Calendar'[Date] = EndDate)
)

VAR StartPct =
CALCULATE(
MAX('TargetIndex'[% of Target]),
FILTER(ALL('Calendar'), 'Calendar'[Date] = PriorEnd)
)

RETURN
SUMX(
Goals,
Goals[2025 Goal] * (EndPct - StartPct)
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
B Manikanteswara Reddy

[ryan_mayu]

could you pls provide some sample data and expected output?

[FormworkFan]

Here are expamples of the two DIM tables I mentioned.  What I would like to do is have the expected Forecast amount for any day of the year and plot this against the YTD Sales. I'd also like to make sure the plot projects into the remaning of the year.

As an example,  for the month of June (end of month), the total goal that should be show would be: 

(0.49117*$3,300.00) = $1,620.86, but the amount that shoudl be shown for June only woudl be

(0.49117 - 0.3810)*$3,300 = $363.56

The Filters will need to work so, if I only selecte "Toys" in Canada and Mexico, this value would be

($100+$75) * (0.49117 - 0.3810) = $19.25 for the month of June

I hope this info is helpful

[v-bmanikante]

Hi @FormworkFan,

Thank you for reaching to Microsoft Fabric community forum.

@ryan_mayu Thank you for your support

To move forward and help you accurately calculate and visualize forecast, could you please clarify a couple of things:

>How are the two tables connected in your model?
Are they both linked through a calendar table or something else?

>Can you let us know where your actual sales amount data is coming from?

Regards,

B Manikanteswara Reddy

[FormworkFan]

My Sales Table are coming from a Salesforce Report. Both the Sales Table and Target Index Table is connected to my calendar table.  Both have a Many to one relationship to the Calendar Table.

The Table withe the Values of the 2025 goals are in a Goals Table that are connect to 2 other DIM Tables for the Divisons and Region. Many to 1.