Connect Year Slicer with two Date Columns per contact (start&end date membership, 1 or 2y duration)

[Temminck]

Hi all,

Hope someone can help! 

I want a slicer with the years 2000 until know. When you click on one year you will receive the total count of members that year and other visuals about those members (ex. membership type, gender,country ....). 

The difficulty is that members can have different membership durations, of 1 or 2 years. This means that one year in the slicer refers to members who have one of the three membership durations. Like in the ‘durations’ table below.

Year slicer	membership duration 1	membership duration 2	membership duration 3
2000	1999-2000	2000-2000	2000-2001
2001	2000-2001	2001-2001	2001-2002
2002	2001-2002	2002-2002	2002-2003
2003	2002-2003	2003-2003	2003-2004
2004	2003-2004	2004-2004	2004-2005
2005	2004-2005	2005-2005	2005-2006
2006	2005-2006	2006-2006	2006-2007
2007	2006-2007	2007-2007	2007-2008
2008	2007-2008	2008-2008	2008-2009
2009	2008-2009	2009-2009	2009-2010
2010	2009-2010	2010-2010	2010-2011
2011	2010-2011	2011-2011	2011-2012
2012	2011-2012	2012-2012	2012-2013
2013	2012-2013	2013-2013	2013-2014
2014	2013-2014	2014-2014	2014-2015
2015	2014-2015	2015-2015	2015-2016
2016	2015-2016	2016-2016	2016-2017
2017	2016-2017	2017-2017	2017-2018
2018	2017-2018	2018-2018	2018-2019
2019	2018-2019	2019-2019	2019-2020
2020	2019-2020	2020-2020	2020-2021

In the 'Membership' database I have the following columns (I included some example rows): 

ContactId	StartDate membership (Date type)	EndDate membership (Date type)	Membership created (Date type)	Other columns with membership info  e.g. Type	Other columns with contact info E.g. country
45689	1 January 2017	31 December 2018	1 January 2017	Trainee	Germany
47546	15 February 2018	31 December 2018	28 April 2017	Active	France
41598	23 March 2018	31 December 2019	16 December 2017	Trainee	Belgium
58964	15 June 2016	31 December 2017	2 February 2016	Retired	Hungary
68465	15 June 2014	31 December 2014	13 April 2013	Retired	Bulgaria
65465	1 January 2015	31 December 2016	14 November 2014	Active	Brazil
65465	1 January 2019	31 December 2019	24 August 2018	Active	Congo
65469	1 January 2018	31 December 2019	1 January 2018	Active	Netherlands

I also created a 'Custom Date' table starting from year 2000 until now. So I can create time intelligence measures. The relationship between 'Membership' and 'Custom date' tables is between column 'Membership created' from Membership table and 'custom dates' from Custom date table with a many to one, single relationship. 

Now with all this I have no idea how I can make PowerBi understand that e.g. year 2018 in the slicer equals all contacts with a

• StartDate membership 2017- EndDate membership 2018
• StartDate membership 2018- EndDate membership 2018
• StartDate membership 2018- EndDate membership 2019

The problem is that I don’t have a unique column. I would like to link the ‘durations’ table with the ‘membership’ table but this is not possible as I don’t have a matching column.

Maybe M language or DAX code can help me with this. But I have not enough knowledge to figure this out.

Does someone out there have an idea? 😮 

Please request more information if not enough was provided.

Thanks already!

[v-juanli-msft]

Hi @Temminck 

Try this method:

add a measure to the visual level filter of the table visual

Measure =
IF (
    (
        YEAR ( MAX ( Membership[StartDate membership] ) )
            = SELECTEDVALUE ( durations[Year slicer] )
            && (
                YEAR ( MAX ( Membership[EndDate membership] ) )
                    IN {
                        SELECTEDVALUE ( durations[Year slicer] ),
                        SELECTEDVALUE ( durations[Year slicer] ) + 1
                    }
            )
    )
        || (
            YEAR ( MAX ( Membership[EndDate membership] ) )
                = SELECTEDVALUE ( durations[Year slicer] )
                && (
                    YEAR ( MAX ( Membership[StartDate membership] ) )
                        IN {
                            SELECTEDVALUE ( durations[Year slicer] ),
                            SELECTEDVALUE ( durations[Year slicer] ) - 1
                        }
                )
        ),
    1,
    0
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Temminck]

Hi  @v-juanli-msft, 
Thank you for the measure. However when applying the measure it duplicates my rows and changes dates...

How did you set the relationship? between:
- Calendar table (all dates from 2000 until 2020)
- Duration table (list of years)
- Membership table (start date, end date, create date,...)

[v-juanli-msft]

Hi @Temminck 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please feel free to let me know.

Best Regards

Maggie

[Temminck]

Thanks already for the help 👌 

I had not the opportunity to completely investigate both solutions as I am working remotely and can not access the network. Waiting for the problem to be fixed… However, I already have the impression both solutions do not completely answer my needs. Will keep you updated as soon as I have access to the data and dashboards again!

[Jimmy801]

Hello @Temminck 

were you able to solve the problem with any reply given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Temminck]

Hi Jimmy801, 

Sorry for the delay, had christmas holidays and sadly no access to the data at home to test answers 😑. Should have waited to post my question. I now know PowerBi has a fast reacting community ! 

But I am back now. The solutions given are not working, I try to understand why, but get lost in this complicated dashboard 😓

[Jimmy801]

Hello

Did you try to build my solution with a empty file? So you are able to understand the solution and if fits to you. Afterwards you can try to apply it to your real world.

Jimmy

[Temminck]

Hi @Jimmy801, 

I tried your solution in an empty file like you suggested and managed to make it work after a while. It now also works in the big file ! 

Huge thanks ❤️ 

Kind regards, Julie

[Jimmy801 · Accepted solution]

Hello @Temminck 

You could try to add a disconnect slicer meaning

Query for your Data called "Table"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJRb4MgEADgv0J8bpqpgPioXbqlyfqw7K3xgdqLJVFcbtKk+zX7LftlA+zMmPWNHHcf3MHhEFHGRR6topjspDYSryR5iDMbSGPyCDV0R0AXErOc7y8bekOpNIBdPQF2Ul+jamXRjFHuChjZwhF/S8R9NhFrUrwj8ccW9aAuztui1DWMXMxyn5iSF4n1ecHybfAglAVXLKFtlOm8yUTO6XjFndHgkvmcdPVJ0INLeoVBIZzs6tnoxm54kQvKWSjSuejPTG8NpwFWmtZiSo4au2l/Z87mnh8zXZN9fyEenwZYovxU7TKW359gYrHCNMRPeMI2vW76yZp9mKXX+J8zeXsYzoCt1KePqKp+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ContactId = _t, #"StartDate membership" = _t, #"EndDate membership" = _t, #"Membership created" = _t, #"Other columns with membership info " = _t, #"Other columns with contact info" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ContactId", Int64.Type}, {"StartDate membership", type date}, {"EndDate membership", type date}, {"Membership created", type date}, {"Other columns with membership info ", type text}, {"Other columns with contact info", type text}})
in
    #"Changed Type"

create query for the years called "Years"

let
    Source = {2000..2020},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Year"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}})
in
    #"Changed Type"

Create a calculated measure

MemberCount = 
VAR Selection =
SELECTEDVALUE ( 'Years'[Year] )
RETURN
CALCULATE(COUNTROWS('Table');filter('Table';AND(YEAR('Table'[StartDate membership])<=Value(Selection);YEAR('Table'[EndDate membership])>=Value(Selection))))

Add Year-Table as filter and a matrix. The result should look like this

with this solution only one year can be selected

Would this fit your needs?

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Temminck]

Hi @Jimmy801 , 

How can I modify this measure

MemberCount = 
VAR Selection =
SELECTEDVALUE ( 'Years'[Year] )
RETURN
CALCULATE(COUNTROWS('Table');filter('Table';AND(YEAR('Table'[StartDate membership])<=Value(Selection);YEAR('Table'[EndDate membership])>=Value(Selection))))

so that if I select a year in the past (in the slicer) I get a table showing member information (id, start date, end date, name, country,...) for the selected year but not those who have a membership this year. 

Example: 

I select year 2017 in the slicer

-> this selection shows members with a membership duration 2016-2016, 2017-2017 and 2017-2018 in a table 

-> members in 2017 who have a membership this year 2020 (2019-2020, 2020-2020, 2020-2021) do not show up in the table

Thanks already for your time!

Btw I soon have a 2day Dax course, will hopefully get better at this 😉   

[Jimmy801]

Hello @Temminck 

so if i got it right... you selelct 2017 and want to see the memebers that have a memebership in 2017 AND 2020

if this is the case try this

MemberCount = 
VAR Selection =
SELECTEDVALUE ( 'Years'[Year] )
RETURN
CALCULATE(COUNTROWS('Table');filter('Table';(YEAR('Table'[StartDate membership])<=Value(Selection)&&YEAR('Table'[EndDate membership])>=Value(Selection))&&(YEAR('Table'[StartDate membership])=2020||YEAR('Table'[EndDate membership])=2020)))

didn't test it, but hopefully works

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Temminck]

Hi @Jimmy801 , 

Think you slightly misunderstood the question. Members in 2017 should NOT show up if they already have a membership this year.

Example:

memberId	startdate membership	enddate membership	country	emailadress
635464	1 jan 2017	31 dec 2017	Germany	julesbram@gmail.com
635464	1 jan 2019	31 dec 2020	Germany	julesbram@gmail.com
523654	1 jan 2016	31 dec 2017	Hungaria	brvaklash@google.com

If I select year 2017 I only want to see the contact 523654, because he has no current membership. Contact 635464 should not diplay because he has a membership in 2017 but also has a membership this year.

The measure should filter rows to 2017 members AND remove duplicate members (2017 and 2020 members). With keeping in mind that 1 year equals 3 membership durations.

[Jimmy801]

Hello @Temminck 

try this measure then

MemberCountWithOutMemberShip2020 = 
VAR Selection =
SELECTEDVALUE ( 'Years'[Year] )
RETURN
if ((CALCULATE(COUNTROWS('Table');filter('Table';AND(YEAR('Table'[StartDate membership])<=Value(Selection);YEAR('Table'[EndDate membership])>=Value(Selection))))) >0 && (CALCULATE(COUNTROWS('Table');filter('Table';OR(YEAR('Table'[StartDate membership])=2020;YEAR('Table'[EndDate membership])>=2020))))=0;1;blank())

All the best

Jimmy

[artemus]

In M:

1. Create a table in m with just the ContactId, EndDate, StartDate

2. Add a custom column with:

{Date.Year(DateTime.Date([StartDate])) .. Date.Year(DateTime.Date([EndDate]))}

3. The click on the column and choose "Expand to new rows"

This will generate one row per year as a number.

4. Make a reference to your date table in a new query.

5. In Transform column-> Date -> Year only

6. Remove all other columns

7. Remove duplicates.

8. Make a one to many relationship between these 2 tables.