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

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.