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.
Hello,
I have two tables: Calendar table (with Data, Year, Qtr, Month and Week fields) and SalesTable (with de Sales field). I need the difference between sales of week previous year (column DIF)
2017 | 2018 | |||
Sales | DIF | Sales | DIF | |
T1 | 730.000,00 | 635.000,00 | -95.000,00 | |
Jan | 91.350,00 | 136.800,00 | 45.450,00 | |
1 | 300,00 | 27.500,00 | 27.200,00 | |
2 | 5.800,00 | 13.500,00 | 7.700,00 | |
3 | 31.000,00 | 37.100,00 | 6.100,00 | |
4 | 28.100,00 | 54.500,00 | 26.400,00 | |
5 | 10.200,00 | 4.200,00 | - 6.000,00 | |
6 | 15.950,00 | 15.950,00 |
I have create the measure DIF:
DIF =
2017 | 2018 | |||
Sales | DIF | Sales | DIF | |
T1 | 730.000,00 | 635.000,00 | 635.000,00 | |
Jan | 91.350,00 | 136.800,00 | 136.800,00 | |
1 | 300,00 | 27.500,00 | 27.200,00 | |
2 | 5.800,00 | 13.500,00 | 7.700,00 | |
3 | 31.000,00 | 37.100,00 | 6.100,00 | |
4 | 28.100,00 | 54.500,00 | 26.400,00 | |
5 | 10.200,00 | 4.200,00 | - 6.000,00 | |
6 | 15.950,00 | 15.950,00 |
It's correct on the week row but it isn't on the subtotals rows for Month and Quarter. What it's wrong?
Thank you very much for all community.
Solved! Go to Solution.
Hi @Raul,
Please refer to the attached sample .pbix file.
Regards,
Yuliana Gu
Hi @Raul,
Please refer to the attached sample .pbix file.
Regards,
Yuliana Gu
Hello @v-yulgu-msft,
One more question about your post. If I want to filter for a year and return the difference with respect to the previous one, without showing the years in columns, how should it be done? Because now if you filter by the year field of the Calendar table, the DIF column returns the same value as the Year column.
Thanks a lot!
EXCELLENT @v-yulgu-msft!!!!
A complex but an effective solution. Thank you very much!!!!
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |