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.
I have problem, but I can't solve it. I made matrix from table with values for places each year. I get Total in column to right but I need difference between column nr. 2 and 10 and ratio in next column. I don't find solution for that.
My example in Matrix table
Place 2011 2012...........2019 Total(but I need difference)
East coast 10.605 11.254 21.859 (649)
How do I solve this?
Best regards,
Jóhannes Finnur
Solved! Go to Solution.
Here is a script to create a datetable.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
If you google Power BI Date Table Script you'll get a bunch of different results along with a bunch of videos that will take you step by step.
Hi,
See if this measure helps
=IF(HASONEVALUE(Data[Place]),[your measure],CALCULATE([your measure],LASTDATE(Calendar[Date]))-CALCULATE([your measure],FIRSTDATE(Calendar[Date])))
You shoould have a Calendar Table and an active relationship between the Date column of your Data Table to the Date column of the Calnedar Table. In the Calendar Table, write this calculated column formula to extract the year
Year = Year(Calendar[Date])
To your visual, drag the Year field from the Calendar Table.
Hope this helps.
Hi Ashish.
Thanks. I'm new to PBI, and just beginning using DAX. You write: "You should have a Calendar Table and an active relationship between the Date column...."
I don't know how to create new table (Calendar Table). Thats my problem (among other things.. 🙂 )
Best regards,
Jóhannes Finnur
Here is a script to create a datetable.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
If you google Power BI Date Table Script you'll get a bunch of different results along with a bunch of videos that will take you step by step.
HI @Anonymous ,
You can use measure formula with if statement to check current row contents level and apply specific calculation formula if they are on total level and use normal summary formula on other levels. (replace original value field)
Measure Totals, The Final Word
After these steps, you can enter to 'subtotals -> column subtotals label' to custom the total level label.
Regards,
Xiaoxin Sheng
Thanks for your answer. In the solution to make new date table?
Thanks, but can you show me the DAX formula. I'm beginner 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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |