Year Difference calculation after performing PIVOT

Question

I am sure this has been asked but after all my work, I could not find solution for it. I have Excel Data into Power BI desktop file like this (I mentioned only main related columns, otherwise there are lot of other columns too)Manufacturer&nbsp; &nbsp; &nbsp; &nbsp; ModelName&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Year&nbsp; &nbsp; &nbsp; &nbsp; Instances&nbsp; &nbsp; &nbsp; &nbsp;.......ColumnNM1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2014&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;..............M1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ..............M1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2014&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ..............M1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ..............M1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;...............and so on...............&nbsp;So I want to get Instances differences between 2014 &amp; 2015 as well as 2016 &amp; 2015 for each Model Name.&nbsp; Logically I should perform PIVOTING first and get In-memory table like this&nbsp;ModelName&nbsp; &nbsp; &nbsp; &nbsp; 2014 Instance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2015 Instance&nbsp; &nbsp; &nbsp; &nbsp; 2016 InstanceAA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 14&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20......&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ..........&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ............&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;.............&nbsp;&nbsp;However I am not able to get this (in-memory) PIVOT table after all my research. Any help please?&nbsp;NOTE: I don't want to display this PIVOT table using Matrix visual, instead as mentioned above I want to get Difference of each Year and then display using Column Chart. For example, Model AA difference between 2015 &amp; 2014 is 6 whereas between 2016 &amp; 2015 is 6.&nbsp;Thanks&nbsp;

zubair_muhammad · Answer

Hi malikirfan28&nbsp;See if this Measure helps you&nbsp;DifferenceFromPriorYear =
VAR LastyearTotal =
    CALCULATE (
        SUM ( TableName[Instances] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Model Name], TableName[Manufacturer] ),
            TableName[Year]
                = SELECTEDVALUE ( TableName[Year] ) - 1
        )
    )
RETURN
    SUM ( TableName[Instances] ) - LastyearTotal

malikirfan28 · Answer

Dear&nbsp;Zubair_Muhammad,I am sorry if I could not explain my question properly. This measure is summing up all year instances&nbsp;BUT I need to calculate difference of each year like 2016-2015 Instances and 2015-2014 instances.&nbsp; You can use following excel data source for verification. I have included "required" sheet what I needed to have.&nbsp;&nbsp;The given measure formula creates output something like this&nbsp;For given data source (pasted image here as well)&nbsp;The resultant charts should be like these:

Forum Discussion

Year Difference calculation after performing PIVOT

2 Replies

Related content

Performance Difference

Performance difference between calculated column or measure?

Dynamic Calculation Different Period

Different Calculations

DAX Performance