Measurehelp

[Anonymous]

This is the example of data i am working on. I want to calculate current year and previous year sale in same row with growth percentage. also i want to use slicer in the report. when i am seleting a current year slicer in my report previous year value is getting blank and also giving growth percent as infinite. I want to see the growth number with the option of slicer in the report

Business Unit	Date	Sales
A	2/2/2019	100
B	2/2/2019	200
C	2/2/2019	300
A	2/3/2019	200
B	2/3/2019	400
C	2/3/2019	800

[Ashish_Mathur · Accepted solution]

Hi,

Try this

1. Create a Calendar Table and write calculated column formulas to extract Year, Month Name and Month number.  Using the Month number column, use the "Sort By" feature to sort the Month names
2. Create a relationship from the Date column of your base data table to the Date column of the Calendar Table
3. Drag Year from the Calendar Table to the column section of the Matrix visual
4. Write these measures

Revenue = SUM(Data[Amount])

Revenue in previuos year = Calculate([Revenue],previousyear(Calendar[Date]))

Growth in revenue over the previous year = iferror([revenue]/[revenue in previous year]-1,blank())

Hope this helps.

[mahoneypat]

Try this measure:

YoY Change = var currentyear = selectedvalue(Date[Year])
var cy = [SalesTotal] // your current measure for sales total

var py = calculate([SalesTotal], all(Date), Date[Year] = currentyear - 1

return Divide((cy-py), py) // will protect you from divide by blank

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat