Forum Discussion

HamidBee's avatar
HamidBee
Icon for Power Participant rankPower Participant
4 years ago
Solved

How do I create a new Table with two columns from two different tables (No measures)?

I am trying to create a new table which contains two columns. Column 1 will calculate the difference between Value 2021 and Value 2020. Column 2 will calculate the percentage change using value 2020 as the base number. I am including the example file below:

 

https://www.mediafire.com/file/26gimtjb8cshhpx/Example.pbix/file

 

Thanks in advance. 

 

Need Help
Show and Tell
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, HamidBee 

    You don't need add a new table, you can directly add calculated columns to the table ‘2021’ as follows:

    Dates_1 = DATEADD('Calendar'[Date],-1,YEAR)
    Value_1 = LOOKUPVALUE('2020'[Value],'2020'[Dates],'2021'[Dates_1],'2020'[Attribute],'2021'[Attribute])
    Difference = '2021'[Value]-'2021'[Value_1]
    Percentage = IF('2021'[Value_1]=0,BLANK(),'2021'[Difference]/'2021'[Value_1])*100

    Result:

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HamidBee if it's just by attribute that you're looking for, you could do this simply by adding an "Attribute" dimension table to your model, and creating a couple simple measures.

    Attributes = DISTINCT(UNION(DISTINCT('2020'[Attribute]),DISTINCT('2021'[Attribute])))



     

     

    Value 2020 = SUM('2020'[Value])


Value 2021 = SUM('2021'[Value])


Difference = [Value 2021] - [Value 2020]


% Change = DIVIDE([Difference],[Value 2020])

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HamidBee what is the desired granularity of this new table?  By date and attribute?  Or just by attribute?  Or just total?

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi HamidBee 

    After adding Table Attribute like Anonymous  proposed you can add this Table according to Attribute Granularity

    It wouldn't make sense to use Date as the Granularity since the 2 tables (2020 & 2021) are containing different values from 2 different years  

    • HamidBee's avatar
      HamidBee
      Icon for Power Participant rankPower Participant
      4 years ago

      I was really only hoping that the month would show as that is what I am comparing. Please see the answer I sent to "ebeery".

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, HamidBee 

        You don't need add a new table, you can directly add calculated columns to the table ‘2021’ as follows:

        Dates_1 = DATEADD('Calendar'[Date],-1,YEAR)
        Value_1 = LOOKUPVALUE('2020'[Value],'2020'[Dates],'2021'[Dates_1],'2020'[Attribute],'2021'[Attribute])
        Difference = '2021'[Value]-'2021'[Value_1]
        Percentage = IF('2021'[Value_1]=0,BLANK(),'2021'[Difference]/'2021'[Value_1])*100

        Result:

        Best Regards,
        Community Support Team _ Eason
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • HamidBee's avatar
    HamidBee
    Icon for Power Participant rankPower Participant
    4 years ago

    Hi. I am trying to create a table that looks like the following:

     

     

    For the dates I only really wanted the month to remain and for their to be only one date column. The difference column is the difference between the 2021 and 2020 value. It is already ordered correctly so row 1 -row 1, row 2-row 2 etc.