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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors