Forum Discussion

HANA's avatar
HANA
Helper I
9 years ago

Create a comparaison report based on the same table

Hello,

 

I have already ask some question. But i need your advice conserning a complex report.

There is what I want to do :

 

 

My problem is more complex because I have several column and 2 Millions lign. I tried to simpliy my problem.

 

Thank you for your help.

Need Help

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    HANA

     

    In this scenario, if you want to show the existing "Supplier" on both periods, it should show "M2" for "2015-1" and "2016-1". When you selelect "2015-1" and "2016-1" in slicer, it will filter all corresponding Supplier records for these two periods. We can filter the Suppliers with Cost for both periods. However, it's not possible to have records "aggregate" on Supplier level and split all costs into different columns. We can onlly CONCATENATEX() those costs into single column.

     

     

    Regards,

     

     

  • Dan80's avatar
    Dan80
    Helper II
    9 years ago
    Import the data table twice via power query.
    In the first table, duplicate year column and minus one from the year (this is to use as a last year reference later).
    Still in first table, merge the second table using a full outer join using the year-1 column, month, supplier and country.
    Select cost data to merge and this will give you current year and last year cost side by side for exactly the same scenario, if there wasn't the scenario, either this year or last year, null will be returned.
    Let us know how you go