Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Rows transformation to columns

Hello Guys,   I have one column with mixed date and year. I need to convert into row format where each column representing a different column with different Month and Year.  Please see the attached...
PowerBI Server
  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    It is suggested to create another table like below:

    Order Columns
    1 202001
    2 202002
    ...

    ...
    12

    202012
    13

    2020-Total
    14

    202101
    ...

    ...
    25

    202112
    26

    2021-Total
    ...

    ...

     

    And then, sort "Columns" column by "Order" column and put "Columns" column into Columns field of the Matrix visual. Then, create a measure like below and put it into Values field:

    Measure =
VAR Year_ =
    LEFT ( MAX ( 'Data'[DATE_ICD] ), 4 )
VAR Columns_ =
    MAX ( 'Table'[Columns] )
RETURN
    IF (
        Columns_ = "2020-Total",
        CALCULATE ( SUM ( 'Data'[Amount] ), Year_ = "2020" ),
        IF (
            Columns_ = "2021-Total",
            CALCULATE ( SUM ( 'Data'[Amount] ), Year_ = "2021" ),
            CALCULATE ( SUM ( 'Data'[Amount] ), 'Data'[DATE_ICD] = Columns_ )
        )
    )

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

     

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

 

You can also create a calculated column like so:

Column =
CONVERT (
    LEFT ( [Date_ICD], 4 ) & "-"
        & RIGHT ( [Date_ICD], 2 ) & "-01",
    DATETIME
)

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.