Forum Discussion

FormworkFan's avatar
FormworkFan
Helper I
1 year ago
Solved

Calculating a Forecast value

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 no...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    1 year ago

    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

ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

could you pls provide some sample data and expected output?

FormworkFan's avatar
FormworkFan
Helper I
1 year ago

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

 

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      FormworkFan
      Helper I
      1 year ago

      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.