Forum Discussion
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 |
|
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 🙂
2 Replies
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 🙂
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 vResultCreate matrix visuals using Dates[Year] as columns:
