Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am wanting to create a visualization that shows the total value of sales per month for this year and last year, and also the variance.
Could someone help me out with the steps I would take to achieve this succesfully?
Solved! Go to Solution.
Hi @Anonymous,
You can use PREVIOUS YEAR function to get the previous year value.
Tables: DateTable(Date), Fact(ID,DATE, VALUE)
Relationship: Date to Date(One to many), both cross filter direction.
Measures:
Get previous year value.
PY = CALCULATE(SUM('Fact'[Value]),SAMEPERIODLASTYEAR('DateTable'[Date]))
Calcualte the diff between current year and previous year
Diff = [PY]- SUM('Fact'[Value])
Create a matrix visual to display the result:
DateTable[Date] to Rows, switch to hierarchy mode and keep year and month, Fact[VALUE] and two measures to Values fields.
Regards,
Xiaoxin Sheng
Hi @Anonymous,
You can use PREVIOUS YEAR function to get the previous year value.
Tables: DateTable(Date), Fact(ID,DATE, VALUE)
Relationship: Date to Date(One to many), both cross filter direction.
Measures:
Get previous year value.
PY = CALCULATE(SUM('Fact'[Value]),SAMEPERIODLASTYEAR('DateTable'[Date]))
Calcualte the diff between current year and previous year
Diff = [PY]- SUM('Fact'[Value])
Create a matrix visual to display the result:
DateTable[Date] to Rows, switch to hierarchy mode and keep year and month, Fact[VALUE] and two measures to Values fields.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
104 | |
92 | |
88 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |