Line chart for 3 different year

Hello,

Before going into the solution, I think it would be better to have it the way it is, as you'd be making the graph larger with no meaningful data for week 15+.

However, if that's still the requirement, you can make a separate table like someone suggested to you. In order to do this, it's best to do it from within Power Query. This code below will make a date table that has multiple columns related to date attributes (like week of year) and it's currently hardcoded to be for 1500 days, starting from the 1st of Jan 2023.

To put it in your file, you'd want to go to "Get Data" -> "Blank Query". Then from Power Query Editor, click on Advanced Editor and paste the below code:

let
    Source = List.Dates(#date(2023,1,1),1500, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Day Name", "Is Weekend?", each if [Day of Week] = 6 then "Yes" else if [Day of Week] = 5 then "Yes" else "No"),
    #"Inserted Start of Month" = Table.AddColumn(#"Added Conditional Column", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Start of Month", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type)
in
    #"Inserted Week of Year"

This would generate the Date Table and when you Close and Apply, it will be loaded into your Power BI Model.

Then what you'd want to do, is link the Date field in your fact table to the Date field in this Date table. Once you'd done that, go to the visual you're working on and swap out the field for the week number to one that's inside the date table.

Then make go to the formatting pane of the visual, then to the x-axis properties and change it to categorical. If it's continuous, it may only show you the weeks which have corresponding values within your measures.

On a side note - it's best practice to have a date table as it helps for filtering multiple fact tables with just one slicer. In your case you only have 1 but the reason for adding this additional dimensional table is because you need to generate the additional dates that you want to show in the visual.

Hi Anonymous ,

Thank you for reaching out to the Microsoft Community Forum.

Please follow below steps:

Create a separate disconnected Week Table (52 weeks) and use that for the x-axis. Modify your DAX for discount_py and discount_ppy so that they ignore the Year slicer and calculate based on the selected year minus 1 (or minus 2).

1. Create a separate Week Table:

Week_Table =
ADDCOLUMNS (
CALENDAR (DATE(2024,1,1), DATE(2025,12,31)),
"Fiscal_Week", WEEKNUM([Date],2)
)

or

Week_Table =
ADDCOLUMNS (
GENERATESERIES(1, 52, 1),
"Week Name", "Week " & [Value]
)

Note: use [Value] as Fiscal Week Number.

2. Create 3 measures.

Discount_CY =
SUMX (
FILTER (
FactTable,
FactTable[Year] = SELECTEDVALUE(FactTable[Year])
&& FactTable[Fiscal_Week] = SELECTEDVALUE(Week_Table[Value])
),
FactTable[Discount]
)

Discount_PY =
SUMX (
FILTER (
FactTable,
FactTable[Year] = (SELECTEDVALUE(FactTable[Year]) - 1)
&& FactTable[Fiscal_Week] = SELECTEDVALUE(Week_Table[Value])
),
FactTable[Discount]
)

Discount_PPY =
SUMX (
FILTER (
FactTable,
FactTable[Year] = (SELECTEDVALUE(FactTable[Year]) - 2)
&& FactTable[Fiscal_Week] = SELECTEDVALUE(Week_Table[Value])
),
FactTable[Discount]
)

3. In the visual: Use Week_Table[Value] on X-axis. Plot the three measures: Discount_CY, Discount_PY, and Discount_PPY on Y-axis.

Note: DO NOT create a relationship between Week_Table and FactTable. Let it stay disconnected. The measures are manually linking based on Fiscal Week inside DAX.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you