Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |