Line Graph, multiple lines required, with a 12 week view

Hi Louise,

Thank you for the forum question.

The solution is: for 24 restaurants, use one line chart with Restaurant as a slicer, Week Start Date on the X-axis, and either multiple hour measures or an unpivoted “Hours Type” field to create the lines.
Recommended structure for your data
Your table should ideally contain one row for each restaurant and week:
Restaurant Week Start Contracted Hours Guaranteed Hours Actual Hours Minimum Hours
Restaurant 01 03/08/2026 520 500 487 450
Restaurant 01 10/08/2026 520 500 505 450
Restaurant 02 03/08/2026 430 420 441 390
You mentioned “contracted, hours, guaranteed hours, actual hours and minimum hours”. That appears to be five fields, although you referred to four lines. The same technique works for either four or five lines.
________________________________________
Quickest solution: use multiple measures
If each hours type is already a separate column, do this:
1. Create measures
Replace HoursData with your actual table name.
Contracted Hours =
SUM ( HoursData[Contracted Hours] )
Guaranteed Hours =
SUM ( HoursData[Guaranteed Hours] )
``
Actual Hours =
SUM ( HoursData[Actual Hours] )
Minimum Hours =
SUM ( HoursData[Minimum Hours] )
If “Hours” is an additional, separate field:
Hours =
SUM ( HoursData[Hours] )
Plain-English explanation
Each measure adds together that type of hours within the current:
• restaurant
• week
• report filter
• slicer selection
2. Build the line chart
Add a standard Line chart, then populate it as follows:
X-axis
Week Start Date
Do not use the automatic Date Hierarchy. Click the dropdown beside the field and select the actual Week Start Date field.
Y-axis
Add all the required measures:
Contracted Hours
Guaranteed Hours
Actual Hours
Minimum Hours
Power BI supports multiple measures on the Y-axis. Each measure becomes a separate line and automatically appears in the legend. Microsoft’s current line-chart documentation explicitly includes displaying multiple line series. [learn.microsoft.com]
Important
Leave the Legend field well empty when you use multiple measures. Power BI uses the measure names as the legend.
3. Add the restaurant slicer
Add a Slicer visual and place:
Restaurant
into the slicer.
The user can then select one of the 24 restaurants, while the same chart updates automatically. This means you do not need to build 24 separate charts or reports.
________________________________________
Make it a rolling 12-week view
You should use a genuine date field such as Week Start Date, not text values such as wk1, wk2, wk3.
Simple visual-filter method
1. Select the line chart.
2. Open the Filters pane.
3. Add Week Start Date under Filters on this visual.
4. Change the filter type to Relative date.
5. Configure it to show dates in the last 12 weeks.
6. Decide whether the current week should be included.
This gives you an automatically moving 12-week period when the dataset refreshes.
More controlled DAX method
If you need the latest 12 weeks available in the imported data, rather than 12 weeks relative to today, create this measure:
Show Latest 12 Weeks =
VAR LatestWeek =
CALCULATE (
MAX ( HoursData[Week Start Date] ),
ALL ( HoursData )
)
VAR CurrentWeek =
MAX ( HoursData[Week Start Date] )
RETURN
IF (
CurrentWeek > LatestWeek - 84
&& CurrentWeek <= LatestWeek,
1,
0
)
Add Show Latest 12 Weeks to Filters on this visual and set it to:
is 1
Why 84?
12 weeks × 7 days = 84 days
This method is useful if the report’s latest data is not always the current calendar week.
________________________________________
Make the X-axis display Wk1, Wk2, Wk3 correctly
A text field such as wk1, wk2, wk3 can cause sorting problems because Power BI may sort it alphabetically:
wk1
wk10
wk11
wk12
wk2
``
Instead, create a numeric week sequence:
Week Sequence =
RANKX (
ALL ( HoursData[Week Start Date] ),
HoursData[Week Start Date],
,
ASC,
DENSE
)
Then create a label:
Week Label =
"Wk" & FORMAT ( HoursData[Week Sequence], "0" )
Select Week Label, then choose:
Column tools → Sort by column → Week Sequence
However, for a report that runs continuously across years, I recommend displaying the actual week commencing date:
Week Display =
"W/C " & FORMAT ( HoursData[Week Start Date], "dd MMM" )
For example:
W/C 08 Jun
W/C 15 Jun
W/C 22 Jun
Sort Week Display by Week Start Date.
This prevents Week 1 from one year being combined with Week 1 from another year. Power BI line charts use categories on the X-axis and numeric values on the Y-axis, so correct category sorting is important. [learn.microsoft.com]
________________________________________
Best long-term solution for 24 restaurants: unpivot the data
For a repeatable report, I would reshape the hours columns in Power Query.
Before unpivoting
Restaurant Week Start Contracted Guaranteed Actual Minimum
Restaurant 01 03/08/2026 520 500 487 450
After unpivoting
Restaurant Week Start Hours Type Hours
Restaurant 01 03/08/2026 Contracted 520
Restaurant 01 03/08/2026 Guaranteed 500
Restaurant 01 03/08/2026 Actual 487
Restaurant 01 03/08/2026 Minimum 450
Power Query steps
1. Select Transform data.
2. Select these columns:
Contracted Hours
Guaranteed Hours
Actual Hours
Minimum Hours
3. Right-click one of the selected headings.
4. Select Unpivot Columns.
5. Rename:
Attribute → Hours Type
Value → Hours
6. Ensure Hours uses a numeric data type.
7. Select Close & Apply.
Build the chart from the unpivoted table
Chart field Column
X-axis Week Start Date
Y-axis Hours
Legend Hours Type
Slicer Restaurant
This is normally the cleaner solution because:
• the legend automatically creates the four or five lines
• new hour categories are easier to accommodate
• filtering is simpler
• formatting individual series is easier
• you need only one Hours measure
The measure would be:
Total Hours =
SUM ( HoursData[Hours] )
Then use:
X-axis: Week Start Date
Y-axis: Total Hours
Legend: Hours Type
________________________________________
Recommended final design
For your weekly restaurant report, I would use:
X-axis = Week Start Date
Y-axis = Total Hours
Legend = Hours Type
Slicer = Restaurant
Visual filter = Latest 12 weeks
Formatting suggestions:
• Actual Hours: thick, dark line
• Contracted Hours: blue line
• Guaranteed Hours: green line
• Minimum Hours: red dashed line
• Markers: on for Actual Hours
• X-axis type: categorical if using week labels
• Data labels: usually off, to prevent clutter
• Tooltip: Restaurant, Week Start, Hours Type and Hours
Most likely reason your attempt failed
The usual causes are:
1. All four fields were placed in Legend instead of Y-axis.
2. The Excel data contains separate rows but no Hours Type column.
3. The fields are stored as text rather than numbers.
4. The X-axis contains text such as wk1 instead of a properly sortable date or week-number field.
5. A field such as Restaurant was added to the line chart’s legend, replacing the intended four hours-series lines.
Best answer: if your data has separate hour columns, either drag all four numeric fields into Y-axis, or preferably unpivot those columns and use Hours Type in Legend. The unpivoted approach is the one I would teach and deploy for a reusable 24-restaurant report.
Why this approach is better
It separates the three reporting dimensions cleanly: week controls the horizontal axis, hours type controls the lines, and restaurant controls the selected location. That structure avoids creating separate visuals for every restaurant and makes the weekly refresh repeatable.




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