Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
92 | |
86 | |
66 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |