one Bar chart with three fact values with a dynamic selected year (2024)

Topic Options

Subscribe to RSS Feed
Mark Topic as New
Mark Topic as Read
Float this Topic for Current User
Bookmark
Subscribe
Printer Friendly Page

All forum topics
Previous Topic
Next Topic

RJV83

Regular Visitor

Mark as New
Bookmark
Subscribe
Mute
Subscribe to RSS Feed
Permalink
Print
Report Inappropriate Content

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

Thursday

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?"

Message 1 of 5

126 Views

All forum topics
Previous Topic
Next Topic

4 REPLIES 4

bhanu_gautam

Super User

Mark as New
Bookmark
Subscribe
Mute
Subscribe to RSS Feed
Permalink
Print
Report Inappropriate Content

Thursday

@RJV83To 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.

Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Message 2 of 5

107 Views

RJV83

Regular Visitor

In response to bhanu_gautam

Mark as New
Bookmark
Subscribe
Mute
Subscribe to RSS Feed
Permalink
Print
Report Inappropriate Content

Friday

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.

Message 3 of 5

56 Views

v-cgao-msft

Community Support

In response to RJV83

Mark as New
Bookmark
Subscribe
Mute
Subscribe to RSS Feed
Permalink
Print
Report Inappropriate Content

8 hours ago

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 posthelps, then please considerAccept it as the solutionto 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

demo.pbix

Message 4 of 5

24 Views

RJV83

Regular Visitor

In response to v-cgao-msft

Mark as New
Bookmark
Subscribe
Mute
Subscribe to RSS Feed
Permalink
Print
Report Inappropriate Content

5 hours ago

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.

Message 5 of 5

8 Views

Helpful resources

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Find out more

Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Join Challenge

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Learn More

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

View All

"); $(".slidesjs-pagination" ).prependTo(".pagination_sec"); $(".slidesjs-pagination" ).append("

"); $(".slidesjs-play.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-stop.slidesjs-navigation").appendTo(".playpause_sec"); $(".slidesjs-pagination" ).append("Play");$(".slidesjs-pagination" ).append("Stop"); } catch(e){ } /* End: This code is added by iTalent as part of iTrack COMPL-455 */ $(".slidesjs-previous.slidesjs-navigation").attr('tabindex', '0'); $(".slidesjs-next.slidesjs-navigation").attr('tabindex', '0'); /* start: This code is added by iTalent as part of iTrack 1859082 */ $('.slidesjs-play.slidesjs-navigation').attr('id','playtitle'); $('.slidesjs-stop.slidesjs-navigation').attr('id','stoptitle'); $('.slidesjs-play.slidesjs-navigation').attr('aria-describedby','tip1'); $('.slidesjs-stop.slidesjs-navigation').attr('aria-describedby','tip2'); /* End: This code is added by iTalent as part of iTrack 1859082 */ });$(document).ready(function() { if($("#slides .item").length < 2 ) { /* Fixing Single Slide click issue (commented following code)*/// $(".item").css("left","0px"); $(".item.slidesjs-slide").attr('style', 'left:0px !important'); $(".slidesjs-stop.slidesjs-navigation").trigger('click'); $(".slidesjs-previous").css("display", "none"); $(".slidesjs-next").css("display", "none"); } var items_length = $(".item.slidesjs-slide").length; $(".slidesjs-pagination-item > button").attr("aria-setsize",items_length); $(".slidesjs-next, .slidesjs-pagination-item button").attr("tabindex","-1"); $(".slidesjs-pagination-item button").attr("role", "tab"); $(".slidesjs-previous").attr("tabindex","-1"); $(".slidesjs-next").attr("aria-hidden","true"); $(".slidesjs-previous").attr("aria-hidden","true"); $(".slidesjs-next").attr("aria-label","Next"); $(".slidesjs-previous").attr("aria-label","Previous"); $(".slidesjs-stop.slidesjs-navigation").attr("role","button"); $(".slidesjs-play.slidesjs-navigation").attr("role","button"); $(".slidesjs-pagination").attr("role","tablist").attr("aria-busy","true"); $("li.slidesjs-pagination-item").attr("role","list"); $(".item.slidesjs-slide").attr("tabindex","-1"); $(".item.slidesjs-slide").attr("aria-label","item"); /*$(".slidesjs-stop.slidesjs-navigation").on('click', function() { var itemNumber = parseInt($('.slidesjs-pagination-item > a.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); });*/ $(".slidesjs-stop.slidesjs-navigation, .slidesjs-pagination-item > button").on('click keydown', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); var itemNumber = parseInt($('.slidesjs-pagination-item > button.active').attr('data-slidesjs-item')); $($('.item.slidesjs-slide')[itemNumber]).find('.c-call-to-action').attr('tabindex', '0'); }); $(".slidesjs-play.slidesjs-navigation").on('click', function() { $.each($('.item.slidesjs-slide'),function(i,el){ $(el).find('.c-call-to-action').attr('tabindex', '-1'); }); }); $(".slidesjs-pagination-item button").keyup(function(e){ var keyCode = e.keyCode || e.which; if (keyCode == 9) { e.preventDefault(); $(".slidesjs-stop.slidesjs-navigation").trigger('click').blur(); $("button.active").focus(); } }); $(".slidesjs-play").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-stop").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-play")) { $(".slidesjs-stop").focus(); } } }); $(".slidesjs-stop").on("click",function (event) { if (event.handleObj.type === "click") { $(".slidesjs-play").focus(); } else if(event.handleObj.type === "keydown"){ if (event.which === 13 && $(event.target).hasClass("slidesjs-stop")) { $(".slidesjs-play").focus(); } } }); $(".slidesjs-pagination-item").keydown(function(e){ switch (e.which){ case 37: //left arrow key $(".slidesjs-previous.slidesjs-navigation").trigger('click'); e.preventDefault(); break; case 39: //right arrow key $(".slidesjs-next.slidesjs-navigation").trigger('click'); e.preventDefault(); break; default: return; } $(".slidesjs-pagination-item button.active").focus(); });});// Start This code is added by iTalent as part of iTrack 1859082$(document).ready(function(){ $("#tip1").attr("aria-hidden","true").addClass("hidden"); $("#tip2").attr("aria-hidden","true").addClass("hidden"); $(".slidesjs-stop.slidesjs-navigation, .slidesjs-play.slidesjs-navigation").attr('title', ''); $("a#playtitle").focus(function(){$("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").mouseover(function(){$("#tip1").attr("aria-hidden","false").removeClass("hidden"); }); $("a#playtitle").blur(function(){$("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#playtitle").mouseleave(function(){$("#tip1").attr("aria-hidden","true").addClass("hidden"); }); $("a#play").keydown(function(ev){if (ev.which ==27) { $("#tip1").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); $("a#stoptitle").focus(function(){$("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").mouseover(function(){$("#tip2").attr("aria-hidden","false").removeClass("hidden"); }); $("a#stoptitle").blur(function(){$("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").mouseleave(function(){$("#tip2").attr("aria-hidden","true").addClass("hidden"); }); $("a#stoptitle").keydown(function(ev){if (ev.which ==27) { $("#tip2").attr("aria-hidden","true").addClass("hidden"); ev.preventDefault(); return false; } }); }); // End This code is added by iTalent as part of iTrack 1859082

User	Count
BeaBF	21
Irwan	20
Jihwan_Kim	16
Greg_Deckler	14
bhanu_gautam	14