Forum Discussion
Temminck
Advocate I
Advocate IPivoting 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 (e...
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
Super User
Super User
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: