Pivoting columns with start and end date

[Temminck]

Hi all,

I am stuck with Pivoting columns in Power Query, Powerbi.

I have table 1 and want to create table 2 and table 3 (see example data below).

If a contact has a one year membership (ex. 2020-2020) he needs to be displayed in the pivoted column of that year (2020).

If a contact has a two year membership (ex. 2019-2020) he needs to be displayed in the pivoted columns of both years (2019 and 2020).

Currently I only manage to display the contact in one of the two columns, but not in both. Anyone knows how to do this ?   

Table 1

ContactId	StartDate membership (Date type)	EndDate membership (Date type)	Other columns with membership info  e.g. Type
45689	2018	2019	Trainee
47546	2018	2018	Active
41598	2018	2019	Trainee
58964	2020	2021	Retired
68465	2020	2021	Retired
65465	2019	2020	Active
65465	2020	2020	Active
65469	2019	2020	Active

Table 2

ContactId	2018	2019	2020	2021
45689	Yes	Yes
47546	Yes
41598	Yes	Yes
58964			Yes	Yes
68465			Yes	Yes
65465		Yes	Yes
65465			Yes
65469		Yes	Yes

Table 3

ContactId	2018	2019	2020	2021
45689	Yes, 2018-2019 Trainee	Yes, 2018-2019 Trainee
47546	Yes, 2018 Active
41598	Yes, 2018-2019 Trainee	Yes, 2018-2019 Trainee
58964			Yes, 2020-2021 Retired	Yes, 2020-2021 Retired
68465			Yes, 2020-2021 Retired	Yes, 2020-2021 Retired
65465		Yes, 2019-2020 Active	Yes, 2019-2020 Active
65465			Yes, 2020 Active
65469		Yes, 2019-2020 Active	Yes, 2019-2020 Active

[Temminck · Accepted solution]

Hi, @DataInsights thank you for the feedback. Good to know how to do this in DAX too! 

I found how to do this in Power Query with the use of This article from The Biccountant 🙂 

[DataInsights]

@Temminck,

This can be done with DAX and a Date table (no relationship between the tables). See measures below:

Membership Year = 
VAR vStartYear =
    YEAR ( MAX ( Memberships[StartDate membership] ) )
VAR vEndYear =
    YEAR ( MAX ( Memberships[EndDate membership] ) )
VAR vYear =
    SELECTEDVALUE ( Dates[Year] )
VAR vResult =
    IF ( vStartYear = vYear || vEndYear = vYear, "Yes" )
RETURN
    vResult

Membership Year with Type = 
VAR vStartYear =
    YEAR ( MAX ( Memberships[StartDate membership] ) )
VAR vEndYear =
    YEAR ( MAX ( Memberships[EndDate membership] ) )
VAR vYear =
    SELECTEDVALUE ( Dates[Year] )
VAR vDisplayText = "Yes, " & vStartYear & "-" & vEndYear & " " & MAX ( Memberships[Type] )
VAR vResult =
    IF ( vStartYear = vYear || vEndYear = vYear, vDisplayText )
RETURN
    vResult

Create matrix visuals using Dates[Year] as columns: