Forum Discussion

malikirfan28's avatar
malikirfan28
Icon for Helper II rankHelper II
8 years ago

Year Difference calculation after performing PIVOT

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 a...
Need Help
Tips and Tricks
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi malikirfan28

 

See if this Measure helps you

 

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's avatar
    malikirfan28
    Icon for Helper II rankHelper II
    8 years ago

    Dear Zubair_Muhammad,

    I am sorry if I could not explain my question properly. This measure is summing up all year instances BUT I need to calculate difference of each year like 2016-2015 Instances and 2015-2014 instances.  You can use following excel data source for verification. I have included "required" sheet what I needed to have. 

     

    The given measure formula creates output something like this

     

    For given data source (pasted image here as well)

     

    The resultant charts should be like these: