YoY charts using measures

Hi Hannah,

Thank you for the forum question.

You're running into the classic date hierarchy vs. continuous date axis issue in Power BI. When you put Month and Day from a hierarchy onto the X-axis, Power BI treats them as categorical, which forces it to render every day under each month — hence the super-wide chart.
What you want is:
• Daily points for the measure (so you don’t lose granularity),
• Monthly tick labels on the X-axis (so it stays readable).
Here’s how to do it cleanly.
________________________________________
The reliable fix: use a continuous Date axis (not the hierarchy)
1. Create a proper Date table (if you don’t already have one) and mark it as a date table:
Columns: [Date], [Year], [MonthNumber], [MonthName], [MonthStart] = EOMONTH([Date],-1)+1.
Modeling ➜ Mark as date table ➜ Select [Date].
2. Use the raw [Date] column (not the hierarchy) in the line chart:
In the X-axis, drag 'Date'[Date] (the single column).
Open Format pane → X-axis:
Type: Continuous.
Concatenate labels: Off (this matters when using hierarchies, but keep it off anyway).
Axis → Ticks / Density: set to Low or Minimum category width higher (if available in your version).
Optional: Zoom slider: On to navigate the range interactively.
With a continuous axis, Power BI automatically spaces labels (months/quarters/years) based on the date span and density, while your measure still evaluates daily. You’ll see points/computed lines at daily granularity, but month names on the axis when the span is large.
3. Ensure Auto Date/Time is off (to avoid hidden model hierarchies overriding behavior):
File ➜ Options and settings ➜ Options ➜ Current File ➜ Data Load ➜ Auto date/time: Off.
________________________________________
If labels are still too dense: two helpful tweaks
• Limit the date range (Format ➜ X-axis ➜ Set Min/Max dates). Fewer months → clearer monthly ticks.
• Increase Minimum category width (Format ➜ X-axis ➜ Minimum category width). This reduces how many labels are drawn and often switches Power BI to monthly ticks over a daily continuous axis.
________________________________________
Alternative pattern (if you must force monthly labels)
If your version/visual still insists on showing daily labels, you can overlay daily values on a monthly axis:
• Axis: 'Date'[MonthStart] (continuous).
• Measure: keep daily measure, but aggregate it in tooltips or a secondary visual:
The line will render at month start dates (you lose the day-to-day curve on that single visual).
Use tooltips: add a table or a line chart with the daily series to a report page tooltip, so hovering a month exposes daily detail.
Or use small multiples by year to preserve YoY readability.
This trades off the daily curve for clean labels; use only if continuous axis doesn’t behave as desired.
________________________________________
YoY measures that work with a continuous axis
A common pattern for YoY using measures and a proper date table:
-- Base measure (daily granularity preserved)
[Sales] :=
SUM ( FactSales[SalesAmount] )

-- Prior year same day
[Sales PY] :=
CALCULATE (
[Sales],
DATEADD ( 'Date'[Date], -1, YEAR )
)

-- Yo-- YoY Delta
[Sales YoY] :=
[Sales] - [Sales PY]

-- YoY %
[Sales YoY %] :=
Then put [Sales] and [Sales PY] (or [Sales YoY %]) on the line chart with 'Date'[Date] as the X-axis (Continuous). You’ll keep daily evaluation while the axis labels show months at practical scales.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector