one Bar chart with three fact values with a dynamic selected year

[RJV83]

Hi everyone, can you help me with the following?

I have three fact tables and a dimension named 'Period,' which is the calendar table. The fact tables are called 'Actual,' 'Budget,' and 'SP,' which stands for Strategic Plan.

I create a bar chart that shows the sales over six years: two closed previous years, the current year with the Budget, and the future three years with the Strategic Plan.

The first two bars are sourced from the 'Actual' fact table:

CALCULATE (
    SUMX ( VALUES ( Period[MonthIndex] ), SUM ( Actual[Amount] ) ),
    FILTER ( Period, [Date] >= DATE ( YEAR ( TODAY () ) - 2, 1, 1 ) ),
    Period[Year] < YEAR ( TODAY () )
) 

The third bar represents the current year and is sourced from the 'Budget' table:

CALCULATE (
    SUMX ( VALUES ( Period[MonthIndex] ), SUM ( Budget[Amount] ) ),
    FILTER ( Period, [Year] = YEAR ( TODAY () ) )
)

The last three bars are sourced from the Strategic Plan ('SP'):

CALCULATE (
SUMX ( VALUES ( Period[MonthIndex] ), SUM ( SP[Amount] ) ),
FILTER ( Period, [Date] >= DATE ( YEAR ( TODAY () ) + 1, 1, 1 ) )
)

In the visual filter, I've placed the 'Relative Year' field from 'Period,' which contains the number of years from the current year, and the filter is set to 'above -2.'

This setup works well, but now the users want to make the current year (the year that shows the budget) dynamic so that they can look back and compare the figures with the present. So the current year with budget needs to be the budget of the selected year. I tried adjusting the "YEAR ( TODAY () )" in all three formulas to use the SelectedValue formula for the year filter in the dashboard, but that way it only shows the budget for the selected year an no previous two years or the three year from Strategic Plan

Does anyone have an idea of how I can make this work?"

[RJV83 · Accepted solution]

I found the solution myself by doing the following (I used the demo file from Gao, thanks for that).

First, duplicate the 'Period' table in Power Query and give it a unique name (in my example, I named it 'Year'). Delete all fields except the 'Year' field. Then, remove all duplicates in the 'Year' field so that all values are unique. You will then have one table named 'Year' with one field named 'Year.'

Next, use the following DAX formulas:

Actual Last 2 Years:

Actual = 
VAR selected_year = SELECTEDVALUE('Year'[Year])
RETURN
CALCULATE (
SUM(Actual[Amount]),
FILTER ( Period, [Year] >= YEAR(DATE ( selected_year - 2, 1, 1 ) )),
Period[Year] < selected_year
) 

 Budget for the Current Year:

Budget = 
VAR selected_year = SELECTEDVALUE('Year'[Year])
RETURN
CALCULATE (
SUM(Budget[Amount]),
FILTER (Period, [Year] = selected_year)
)

Strategic Plan:

Strategic Plan Value = 
VAR selected_year = SELECTEDVALUE('Year'[Year])
RETURN
CALCULATE (
SUM(SP[Amount]),
FILTER (Period, [Date] >= DATE(selected_year + 1, 1, 1))
)

Then, place the 'Year' field from the 'Period' table on the X-axis and the 'Actual,' 'Budget,' and 'Strategic Plan' measures on the Y-axis. Create a year filter using the 'Year' field from the 'Year' table. And you’re done.

Again, thanks to Gao for putting me on the right track!

See here the demo file for more info: Demo.pbix 

[bhanu_gautam]

@RJV83 To make the current year dynamic and allow users to select a year for comparison while still showing the previous two years and the next three years, you can use the SELECTEDVALUE function in DAX to capture the selected year.

Capture the Selected Year:
DAX
VAR SelectedYear = SELECTEDVALUE(Period[Year])

Calculate the Actuals for the Previous Two Years:
DAX
CALCULATE (
SUMX ( VALUES ( Period[MonthIndex] ), SUM ( Actual[Amount] ) ),
FILTER ( Period, [Date] >= DATE ( SelectedYear - 2, 1, 1 ) ),
Period[Year] < SelectedYear
)
Calculate the Budget for the Selected Year:
DAX
CALCULATE (
SUMX ( VALUES ( Period[MonthIndex] ), SUM ( Budget[Amount] ) ),
FILTER ( Period, [Year] = SelectedYear )
)

Calculate the Strategic Plan for the Next Three Years:
DAX
CALCULATE (
SUMX ( VALUES ( Period[MonthIndex] ), SUM ( SP[Amount] ) ),
FILTER ( Period, [Date] >= DATE ( SelectedYear + 1, 1, 1 ) )
)

Adjust the Visual Filter: Ensure that the visual filter for 'Relative Year' is set to include the range from SelectedYear - 2 to SelectedYear + 3.

Here is the combined DAX formula for your bar chart:

VAR SelectedYear = SELECTEDVALUE(Period[Year])

RETURN
SWITCH(
TRUE(),
Period[Year] = SelectedYear - 2 || Period[Year] = SelectedYear - 1,
CALCULATE (
SUMX ( VALUES ( Period[MonthIndex] ), SUM ( Actual[Amount] ) ),
FILTER ( Period, [Date] >= DATE ( SelectedYear - 2, 1, 1 ) ),

This approach ensures that the selected year is dynamic and the calculations for the previous two years, the current year, and the next three years are adjusted accordingly.

[RJV83]

Thank you for your reply.

When I do that, I get the same result as I mentioned before. I only see the budget for all years, but no Actuals and no SP data. Also how can I make the relative year filter based on the selected year instead of the current year.

[Anonymous]

Hi @RJV83 ,

Please try the follow measures:

Actual Value1 = 
VAR __selected_year = SELECTEDVALUE('Period'[Year])
VAR __result =
CALCULATE (
    SUM ( Actual[Amount] ),
    FILTER ( ALL(Period), [Date] >= DATE ( __selected_year - 2, 1, 1 ) ),
    Period[Year] < __selected_year 
) 
RETURN
__result

Actual Value2 = 
VAR __selected_year = SELECTEDVALUE('Period'[Year])
VAR __result =
CALCULATE (
    SUM ( Actual[Amount] ),
    FILTER ( ALL(Period), [Date] >= DATE ( __selected_year - 1, 1, 1 ) ),
    Period[Year] < __selected_year 
) 
RETURN
__result

Budget Value = 
VAR __selected_year = SELECTEDVALUE('Period'[Year])
VAR __result =
CALCULATE (
    SUM ( Budget[Amount] ),
    FILTER (ALL( Period), [Year] = __selected_year )
)
RETURN
__result

Strategic Plan Value = 
VAR __selected_year = SELECTEDVALUE('Period'[Year])
VAR __result =
CALCULATE (
SUM ( SP[Amount] ),
FILTER ( ALL(Period), [Date] >= DATE ( __selected_year + 1, 1, 1 ) )
)
RETURN
__result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[RJV83]

But now everything is posted as 2024 figures. Plus Actual Value 1 is the sum of 2022 and 2023 instead of only 2022 when you select 2024 as Selectedyear.