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 All - Having trouble calculating percentage difference between periods. I have 2 tables, 1 with Dates and 1 with Product data, Customers and Dates. In the report are 1 Slicer for months and 1 Matrix with Years as columns, Product as rows and Distinct Counts of Customers as values. I'd like to add to the Matrix the year over year % difference of customers when multiple months are selected in the Slicer but can't seem to figure it out. Anyone have any ideas?
Solved! Go to Solution.
@hdsogno , this will come by time intelligence using this year vs last and their diff. Power Bi do not have column diff.
Can you share actual data on which you need this, make me @
Thanks for the reply but not what I'm looking for. I have a date table already. What I'm looking for is a calculation for the percent difference to produce results like this:
2019 2020 % Dif
Bolts 4 4 0%
Nuts 2 5 150%
Screws 6 5 - 16%
Total 8 9 12%
You may try ISINSCOPE to change the total.
@hdsogno , this will come by time intelligence using this year vs last and their diff. Power Bi do not have column diff.
Can you share actual data on which you need this, make me @
amitchandak - There was something wrong with my date table. I fixed it and your solution did work.
Thank you.
Create a date calendar, Have these months these and try Year formula, It should respond to month filter
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |