cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Temminck
Advocate I
Advocate I

Pivoting columns with start and end date

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

 

 

1 ACCEPTED SOLUTION
Temminck
Advocate I
Advocate I

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 🙂 

View solution in original post

2 REPLIES 2
Temminck
Advocate I
Advocate I

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 🙂 

View solution in original post

DataInsights
Super User II
Super User II

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

 

DataInsights_0-1610387902311.png

 

DataInsights_1-1610387915738.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.