Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

how to transform the data use DAX

Hi, Here is my data any and I need to create a visual that show the data both this year the previous year, but the x-axis should be current year, so first I need to transform the data, and the result of the data of current year and previous year should be in the same row, do you have any ideas how to transform the Rev Last of 2021 to 2022? Thank you.

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please try below steps:

    1. below is my test table

    Table:

    2. create a measure with below dax formula

    Measure =
VAR cur_ym =
    SELECTEDVALUE ( 'Table'[year_month] )
VAR cur_year =
    INT ( LEFT ( cur_ym, 4 ) )
VAR cur_month =
    INT ( RIGHT ( cur_ym, 2 ) )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        INT ( LEFT ( 'Table'[year_month], 4 ) = cur_year - 1 )
    )
VAR _val =
    CALCULATE (
        MAX ( 'Table'[Rev Last] ),
        ALL ( 'Table' ),
        INT ( LEFT ( 'Table'[year_month], 4 ) ) = cur_year - 1,
        INT ( RIGHT ( 'Table'[year_month], 2 ) ) = cur_month
    )
RETURN
    _val

    3. add a table visual with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • SwayamSinha's avatar
    SwayamSinha
    Icon for Microsoft Employee rankMicrosoft Employee
    3 years ago

    If you looking for something as above then its as simple as the below code:

    PyQty = Calculate (Sum(Order_Details[Quantity]),DATEADD('Date'[Date],-1,Year))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks! the date column type is text so it sees this function did not solve the problem directly

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please try below steps:

    1. below is my test table

    Table:

    2. create a measure with below dax formula

    Measure =
VAR cur_ym =
    SELECTEDVALUE ( 'Table'[year_month] )
VAR cur_year =
    INT ( LEFT ( cur_ym, 4 ) )
VAR cur_month =
    INT ( RIGHT ( cur_ym, 2 ) )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        INT ( LEFT ( 'Table'[year_month], 4 ) = cur_year - 1 )
    )
VAR _val =
    CALCULATE (
        MAX ( 'Table'[Rev Last] ),
        ALL ( 'Table' ),
        INT ( LEFT ( 'Table'[year_month], 4 ) ) = cur_year - 1,
        INT ( RIGHT ( 'Table'[year_month], 2 ) ) = cur_month
    )
RETURN
    _val

    3. add a table visual with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.