Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I'm looking for advice as I'm lately getting a lot of questions from my audience about year. My dashboard shows various campaigns and campaign types that we have been running over the years. Users can filter using the "campaign start" filter and see how many campaigns we have been running per country on that year. However, when the campaign is running longer than the calendar year, the count does not consider it, as it started in the previous year. See below in the example table, if the users select 2021, there would be only 2 campaigns (in France and Italy), however technically during 2021 we had 3 campaigns including Germany. I'm not sure how to ensure the year filter covers the following year.
Any suggestions/ideas?
Thanks
Campaign Start | Campaign End | Campaign Country |
13 Nov 2020 | 13 May 2021 | Germany |
01 Feb 2021 | 01 Aug 2021 | France |
05 May 2021 | 05 Nov 2021 | Italy |
Solved! Go to Solution.
Hi @Anonymous
I think you want to filter your visual by "Select Year" >="Campaign Start Year" and "Select Year"<="Campaign End Year". It is not a good way for you to create a relationship between your "Date" table and Fact Data table.
Here I suggest you to create an unrelated date table and create a filter measure to achieve your goal.
My Sample:
If I select 2021 in slicer, based on your logic, I should get result Germany, France, Italy and B.
I create an unrelated date table by dax.
Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))
Measure:
Filter =
VAR _Select_Year =
SELECTEDVALUE ( 'Date'[Year] )
VAR _Start_Year =
YEAR ( MAX ( 'Table'[Campaign Start] ) )
VAR _End_Year =
YEAR ( MAX ( 'Table'[Campaign End] ) )
RETURN
IF (
ISFILTERED ( 'Date'[Year] ),
IF ( AND ( _Start_Year <= _Select_Year, _End_Year >= _Select_Year ), 1, 0 ),
1
)
I create a table visual, add this measure into filter field in this visual and set it to show items when value = 1.
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , expected output is not clear
see one of the 2 can help
Thanks @amitchandak the expected outcome would be 3. When I select the year filter as 2021, it should show all campaigns that started or continued during 2021.
@Anonymous , Try a measure like, May with an independent date or year table
measure =
var _max = year(maxx(allselected('Date'), 'Date'[Date]))
return
countrows(filter(Table, _max>= year([Start Campaign]) && _max <= year([End Campaign]) ))
Hi @amitchandak Many thanks for your answer but I'm getting a syntax error from the measure you shared.
Hi @Anonymous
I think you want to filter your visual by "Select Year" >="Campaign Start Year" and "Select Year"<="Campaign End Year". It is not a good way for you to create a relationship between your "Date" table and Fact Data table.
Here I suggest you to create an unrelated date table and create a filter measure to achieve your goal.
My Sample:
If I select 2021 in slicer, based on your logic, I should get result Germany, France, Italy and B.
I create an unrelated date table by dax.
Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))
Measure:
Filter =
VAR _Select_Year =
SELECTEDVALUE ( 'Date'[Year] )
VAR _Start_Year =
YEAR ( MAX ( 'Table'[Campaign Start] ) )
VAR _End_Year =
YEAR ( MAX ( 'Table'[Campaign End] ) )
RETURN
IF (
ISFILTERED ( 'Date'[Year] ),
IF ( AND ( _Start_Year <= _Select_Year, _End_Year >= _Select_Year ), 1, 0 ),
1
)
I create a table visual, add this measure into filter field in this visual and set it to show items when value = 1.
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Fantastic @v-rzhou-msft It works marvellously on a table. However, when I wanted to put this measure as a filter across the page it doesn't allow me, nor does it allow me to put on a card visual. Is there a way of achieving this?
Thank you
Hi @Anonymous ,
Like your test, Power BI doesn't support us to add measures in Page/All Page level filter. We could only add measures in visual level filter in Power BI. You can add this measure in other visuals' visual level filter and set it to show items when value =1.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks once again @v-rzhou-msft but it seems I can't add it to a card visual's visual level filter either (below is a screenshot from your pbix), but considering this is a PBI limitation, I guess this is the most that can be done.
Thanks,
User | Count |
---|---|
128 | |
73 | |
70 | |
58 | |
53 |
User | Count |
---|---|
193 | |
96 | |
66 | |
62 | |
52 |