Forum Discussion
Advocate IYOY Trend chart - X-axis sort
This is my current trend chart, with YOY calculated based on the term selection.
The x-axis is the specific events happening during the term, and it falls on a similar timeline PY and CY. Now, the issue is I am not able to sort the x-axis by date; the CY and PY are plotted in different slots (2nd pic) because they don't cover the same time period. Only the event names are the same. How to handle this issue? Also, the events differ by term; depending on the term selected, we may see fewer events for other terms. How to handle this issue with a better design? Sequencing the events may be different for each term. How to accomplish this
If your PY and CY numbers dont share the same set of categories - one exists in PY but not in CY and vice versa - you'll see broken lines and for hierarchical categories and text dimensions, they always are categorical. Instead of using the event names as dimensions, use an event order column and let the tooltip handle the event name.
Please see the attached pbix
HI RajiKarthik,
Have you had a chance to review the solution shared by Prince0011, DataVitalizer, krishnakanth240, danextian ? If the issue persists, feel free to reply so we can help further.
Thank you.
6 Replies
- DataVitalizer
Super User
Hi RajiKarthik
The reason they don't line up is that your axis has both the event name and the term on it, so "Registration tracking 14" for 2025 and for 2026 end up as two separate points.
I'd take the term off the axis and keep only the event name. Then create a small event table (one row per term type and event) with a sort column, like the average number of days from term start across past years, and sort the event name by that. Your CY and PY measures will then share the same axis and sit in the right order.
Since that table is tied to the term type, picking a term will only show that term's events. Just make sure your measures return blank instead of 0 when there's no data.
If the events vary a lot between terms, another option is to use "days from term start" as a continuous x-axis and put the event name in the tooltip. It's less readable on the axis, but it needs no sorting.
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡 - krishnakanth240
Hi RajiKarthik
You can sort by creating an Sort_Order calculated column which assigns each event the same order number across CY and PY unlike calendar date then sort EVENT_OF_SNAP_DESC by that column
- danextian
If your PY and CY numbers dont share the same set of categories - one exists in PY but not in CY and vice versa - you'll see broken lines and for hierarchical categories and text dimensions, they always are categorical. Instead of using the event names as dimensions, use an event order column and let the tooltip handle the event name.
Please see the attached pbix
v-saisrao-msftCommunity Support
HI RajiKarthik,
Have you had a chance to review the solution shared by Prince0011, DataVitalizer, krishnakanth240, danextian ? If the issue persists, feel free to reply so we can help further.
Thank you.
- Prince0011
Solution Sage
Hi,
Add a helper "Event Order" column (1, 2, 3...) representing each event's sequence within a term, then sort the x-axis by that instead of date. This aligns CY and PY on the same slots regardless of actual date gaps.
For differing events per term, use a mapping table per term with its own sequence, so each term pulls only its relevant events in order.
If this helped, feel free to give it a kudos or mark it as solution, helps others find it too.
RajiKarthikThank you all, sorting seq_no for the events helped!
