Forum Discussion

fjjpeeters1976's avatar
fjjpeeters1976
Icon for Helper III rankHelper III
1 year ago
Solved

Actuals and forecasts - scenarios

hi all,

 

I have a big table with cost data, this includes several forecast scenarios. These forecast scenarios are labelled for example FC09_2025, this scenario has data in there for 2025 and 2026 (no data in there for 2024). Data for past years are put in a scenario called MA (management actuals). I want to create a report where the user can select with a slicer the forecast scenarios FC09_2025, FC08_2025 etc. 

I would like to show a barchart with the years on the x-axis, however I want to have the previous years included in the barchart. So it should take the previous years that are not in the current forecast scenarios from the MA scenario. I have tried several ways with countrows or values but so far I could not yet make it work.

 



  • fjjpeeters1976's avatar
    fjjpeeters1976
    1 year ago

    Hi, thank you for your reply. But in your example the graph also only shows 2025 and 2026 when I select the scenario FC09. I would like it to show data from 2023 until 2026

4 Replies

  • Cookistador's avatar
    Cookistador
    Icon for Super User rankSuper User
    1 year ago

    Hi  fjjpeeters1976 

     

    The following dax formula should help you

    Combined Cost =
    VAR SelectedScenario = SELECTEDVALUE('Table'[Scenario])
    VAR ForecastStartYear =
        IF(
            ISBLANK(SelectedScenario) || SelectedScenario = "MA",
            9999,
            VALUE(RIGHT(SelectedScenario, 4))
        )

    VAR CurrentYearInChart = MAX('Table'[Year])

    RETURN
        IF(
            CurrentYearInChart < ForecastStartYear,
            CALCULATE(
                SUM('Table'[Cost]),
                'Table'[Scenario] = "MA"
            ),
            CALCULATE(
                SUM('Table'[Cost]),
                'Table'[Scenario] = SelectedScenario
            )
        )
     

     

     

    I joint the power BI, I created to create the dax

    • fjjpeeters1976's avatar
      fjjpeeters1976
      Icon for Helper III rankHelper III
      1 year ago

      Hi, thank you for your reply. But in your example the graph also only shows 2025 and 2026 when I select the scenario FC09. I would like it to show data from 2023 until 2026

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    You could create a calculation group to check the minimum year for which you have data for the currently selected scenario. If you don't have data for the current year in the chart then show the data for MA, otherwise show the currently selected scenario

    Calc item =
VAR CurrentYear =
    SELECTEDVALUE ( 'Date'[Year] )
VAR MinYearWithData =
    YEAR ( CALCULATE ( MIN ( 'Cost Data'[Date] ), ALLSELECTED ( 'Date' ) ) )
VAR Result =
    IF (
        MinYearWithData <= CurrentYear,
        SELECTEDMEASURE (),
        CALCULATE ( SELECTEDMEASURE (), 'Cost Data'[Scenario] = "MA" )
    )
RETURN
    Result
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hello fjjpeeters1976,
    Thank you  for reaching out to the Microsoft fabric community forum.

    I tried this scenario in Power BI with some sample data. Using a separate Year table (2023–2026) and a DAX measure that switches between MA for the earlier years and the selected forecast scenario for later years, the chart shows the full range of years as expected.

    For example, when I select FC09_2025, the chart displays 2023 and 2024 from MA, and then 2025–2026 from FC09_2025. If I change the slicer to FC08_2025 or FC_AOP_2025, the logic still holds.

    Please find the attached file for your reference.
     


     

    Hope this helps if you have any queries we are  happy to assist you further.
    Best Regards,
    Harshitha.