How to find the difference in values over different years in Visualistion table in PowerBI

[sunilsalian689]

Hello Community!

I have to some readings taken against a particular items multiple times over different years. Below mentioned is the visualisation page which is showing based on the filtering of the ID. Basically now I need to difference between the values of readings between each year and also the difference of readings for the first and the last readings which can lead to some analysis lateron.

Will it be possible to add the column in the visualisation table itself to show the differences between the values over subsequent years .

Note : Below table is just the representation of how the visual looks like in the powerBI when filtering 1 ID's. The years can be different for the different ID's

Can anyone suggest me what is the best possible way I can perform this?

Thanks in advance.

[Anonymous]

Hi @sunilsalian689 ,

Looks like the screenshot is missing? It would be easier to get the solution if you could directly show some sample data and expected result to us.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 

Best Regards,

Jay

[sunilsalian689]

Thanks for the reply  Jay!

I am hereby attaching the snippet of what currently the visualisation looks like after filter one item. Basically when filtering equipment 1, I would be having readings taken over different years. This example is having 2010,2013 & 2016. Not necessary that the years will be the same while filtering other equipments.

Now the question is based on the visuals obtained as per the year values, Will it be possible to add a column which compares the difference and also the rate  which is expected as shown in the snip of excel.

Below this image represents the matrix visualisation table which shows value in different years. 

Below Snip is the calculation done in the excel which is highlighted in yellow and this is expected result to be obtained in powerBI


SW 1= value of 2010-value of 2013
Short CR 1 = (value of 2010-value of 2013)/(difference between 2010&2013) in years
SW 2= value of 2013-value of 2016
Short CR 1 = (value of 2013-value of 2016)/(difference between 2010&2013) in years,

Long WL =value of first year (2010) - Value of the latest year(2016)
Long CR = value of first year (2010) - Value of the latest year(2016)/((difference between 2010&2016) in years.

Snip of excel

I would need help to replicate the calculation to be done in power BI matrix visual page and then I can conditional format the derived values from the calculation.

Note : The years will be different when we will filter the different equipments.

[amitchandak]

@sunilsalian689 , You can use date/year table and time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA