Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |