Working with year that goes beyond a calendar year

[Anonymous]

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

[amitchandak]

@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

[Anonymous]

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. 

[amitchandak]

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

Hi @amitchandak Many thanks for your answer but I'm getting a syntax error from the measure you shared.

[v-rzhou-msft · 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:

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]

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

[v-rzhou-msft]

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]

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,