Forum Discussion
Difference between 2 values in table
Hi,
I need some help! I have been trying all day but to no avail.
I am trying to calculate the year difference in columns in a table. I have attached the model and table view below.
Any help would be appreciated!
I This is the data modelI want to compare 2017 to 2018, 2018 to 2019 and so on
Anonymous , Create a separate year table. Say date
Date = Distinct(Table[Year])
Use these
//Only year vs Year, not a level below
This Year = CALCULATE(sum('Table'[Value]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Value]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Use matrix Visual - https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
2 Replies
Hi Anonymous
Do you have a short date column in your data model?
Anonymous , Create a separate year table. Say date
Date = Distinct(Table[Year])
Use these
//Only year vs Year, not a level below
This Year = CALCULATE(sum('Table'[Value]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Value]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Use matrix Visual - https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
