Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Anonymous
Not applicable

Working with year that goes beyond a calendar year

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 StartCampaign EndCampaign Country
13 Nov 202013 May 2021Germany
01 Feb 202101 Aug 2021France
05 May 202105 Nov 2021Italy
1 ACCEPTED 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:

1.png

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.

1.png

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.

 

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@Anonymous , expected output is not clear

 

see one of the 2 can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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])  ))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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:

1.png

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.

1.png

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
Not applicable

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.

Anonymous
Not applicable

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,

thatakke_0-1639388975486.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

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

Jan NL Carousel

Fabric Community Update - January 2025

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