Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I want to display data ranging from November 2016 to now (March 2017, but will keep growing) in a line chart. My date field is a date (properly recognized as a date Hierarchy) and I want to keep the hiearchy mode and provide my users the ability to drill up-down through days, months, quarters, years.
Issues are the following: Power BI does not sort or aggregate properly.
- At the Day level the data is aggregated on the day numerical value (1 thru 30/31) so my axis has 0-31 data points instead of the expected 120 or so entries for the last 4 months.
- At the month level the data is sorted by month, but irregardless of the year. So I get : Jan, Feb, Mar, Nov, Dec Instead of the expected Nov, Dec, Jan, Feb, Mar (Nov and Dec being in 2016 while Jan, Feb, Mar are in 2017).
- At the Quarter level: same as for months. Q1 comes before Q4. I am expecting Q4 (2016) to come before Q1 (2017)
Any solutions to this ?
examples:
thanks
Solved! Go to Solution.
First question comes in my mind, do you have dedicated continuous calendar table? if not follow this link as example to create one and then use the fields from calendar table on your axis to achieve what you are looking for. Let me know if you need further help.
Thanks,
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
First question comes in my mind, do you have dedicated continuous calendar table? if not follow this link as example to create one and then use the fields from calendar table on your axis to achieve what you are looking for. Let me know if you need further help.
Thanks,
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you Parry2k!
You put me on the right track. I ended up creating a Calendar table using your instructions (not as many fields and tweaking the syntax as my French Operationg System doesn't like the ";" ).
I was then able to link my source table to the Calendar on the date field and simply use the Calendar day, year-month and year-quarter as the elements of my axis hierarchy. Works like a charm.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |