Difference of the 2 columns to create a new column

[Anonymous]

Hi,

I have a requirement where there are 2 tables Oct_20 and May_22. The first table is Oct table and second is May table. My requirement is to get a new table with difference of these two tables. Like for 2012 0-3 = -3(Laptop), 26-67=-41(Workstation) for 2013 2-26=-24(laptop), 8-14=-6(Workstation) and so on. Please suggest on how to achieve this.

This is how the data looks like in each table as shown below. I need to find diff between count of asset type in 1st table and count of asset type in seconf table table.

 

Thanks in Advance,

Neelofar.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please create the table.

NewTable = 
VAR tab =
    DISTINCT (
        UNION (
            SUMMARIZE ( May_22, May_22[Model Year], May_22[Type] ),
            SUMMARIZE ( Oct_20, Oct_20[Model Year], Oct_20[Asset Type] )
        )
    )
RETURN
    ADDCOLUMNS (
        tab,
        "DiffValue",
            CALCULATE (
                SUM ( May_22[Count of Type] ),
                FILTER (
                    May_22,
                    May_22[Type] = EARLIER ( [Type] )
                        && May_22[Model Year] = EARLIER ( [Model Year] )
                )
            )
                - CALCULATE (
                    SUM ( Oct_20[Count of Asset Type] ),
                    FILTER (
                        Oct_20,
                        Oct_20[Asset Type] = EARLIER ( [Type] )
                            && Oct_20[Model Year] = EARLIER ( [Model Year] )
                    )
                )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create common dimensions Type and model year, join with both tables and the use measure sum/ count of asset type from both tables in a visual

example

Type = distinct(union(distinct(Table1[Type]),distinct(Table2[Type])))

Use matrix visual, as per need

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19