Hi ,
I have a matrix where one of the fields is a Year column, so I have Year 2021 and Year 2022.
As a filter I am using the new Fields Parameter. How can I calculate the Year over Year percentage so that the Year over Year calculation updates automatically.
Will it be through DAX or using Quick Measures.
Kindly advice the solution.
Many thanks,
Mustafa
Hi @chris786 , does the DIVIDE function doesn’t suit your use case?
The problem is that Year is a field in the master data, so in the matrix Year is put on columns, so if I use the divide function it dosen’t work. Also, in my formula, I would like a dynamic calculation instead of hardcoding the Year numbers.
YoY% =
var Sales2020 = CALCULATE([Total Sales],IMS[Year (YYYY)]=2020)
var Sales2021 = CALCULATE([Total Sales],IMS[Year (YYYY)]=2021)
return
DIVIDE(Sales2020,Sales2021)
@chris786 , so basically you just wanted to have a current year comparing to prior year? First, you need to create a date table and have the relationship with your master data. Then create the prior year calculation using SAMEPERIODLASTYEAR function. Lastly, create the measure that you have on YoY% if that makes sense?
Sales Prior Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
YoY% = DIVIDE([Total Sales], [Sales Prior Year],0)
