Dynamic Comparison of Two Periods with a Synchronized Time Axis in Power BI

[akim_no]

I am setting up a comparison between two periods in Power BI. The idea is that the user freely selects two date ranges via slicers: a Period 1 and a Period 2.

These selections are then used to dynamically bound DAX measures, and so far, everything is working correctly.

Now, what I want to do is display these two periods simultaneously in a chart, but with a common time axis.

The problem is that the periods can span entirely different dates (for example, January 2023 for Period 1 and February 2024 for Period 2). If I use the actual date as the axis, the two curves will be shifted, so the comparison no longer makes sense visually.

What I need is a virtual or dynamic axis that takes, for example, the dates of Period 1 as a reference and automatically aligns the curves of Period 1 and Period 2 on this axis. In other words, the two curves would start at the same position on the axis, regardless of their actual date in the calendar.

This way, I could compare the evolution of both periods day by day while keeping the display synchronized.

Can anyone help me with this issue?

[v-sgandrathi · Accepted solution]

Hi @akim_no 

You're correct, the challenge you're now facing comes from the static nature of the RelativeDays table and how the measures are currently structured. Let's break it down and improve both aspects:

The axis should reflect only the number of days present in the selected periods. Instead of using a fixed GENERATESERIES(0, 60, 1) you can dynamically calculate the maximum number of days between the two periods and use that as the upper bound, or alternatively, filter your visuals/measures to show only valid days based on available data.

In your current measure, the offsets might be misaligned. Instead, consider this simplified logic for each period:

Period1_Relative = 
VAR StartDate1 = MIN('Period1Table'[Date])
VAR RelativeDay = SELECTEDVALUE('RelativeDays'[Value])
VAR TargetDate = StartDate1 + RelativeDay
RETURN
CALCULATE(
    AVERAGE('DataTable'[Value]),
    'DataTable'[Date] = TargetDate
)

This aligns each period from Day 0 onwards. Repeat the same for Period 2 with its respective start date.

To avoid extra blank space or irrelevant data on the axis:

• Use visual-level filters to show only days where data exists and ensure your DAX doesn't compute values beyond the selected range.

Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.

Thank you.

[v-sgandrathi]

Hi @akim_no,

You’re absolutely on the right track with your approach!

Since your two periods can have completely different actual dates (like Jan 2023 vs Feb 2024), using the calendar date as the X-axis won’t help in visual comparison, the lines will look shifted. So instead, we need to normalise the timeline.

The idea is to create a virtual or relative time axis, like Day 1, Day 2, Day 3, and so on, starting from the beginning of each selected period. This way, you can compare both periods side by side. 

To implement this:

• Use an integer-based axis instead of actual dates.
• Then, create DAX measures that calculate values for each period based on how many days have passed since that period started.
• Next, plot both measures together on the same chart using the relative day number on the X-axis.

If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!

Thank you.

[akim_no]

Thank you so much for your response! I'm a bit confused about how to proceed exactly. Should I create a table for the virtual or relative time axis and then use it in the measures? Right now, I'm not quite sure how to structure this. Could you please help clarify that part? Thanks in advance for your help!

[v-sgandrathi]

Hi @akim_no,

To align both selected periods on a common axis, you need to create a separate table that represents a virtual or relative time axis, essentially a list of sequential day numbers (e.g., Day 1, Day 2, Day 3, and so on). This will allow both periods to be displayed side-by-side starting from their respective Day 1, even if the actual calendar dates are different.

You can create this axis table in Power BI using the following DAX:

RelativeDays =

ADDCOLUMNS(

    GENERATESERIES(1, 60, 1),

    "DayLabel", "Day " & [Value]

)

Once this is set up, you can create two DAX measures (one for each period) that:

Determine the start date of the selected period add the relative day value to compute the actual date to look up then return the corresponding metric value for that date.

If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!

Thank you.

[akim_no]

The issue is that the axis isn't dynamic and is limited to the number of days I define in the table. In my example, I selected 30 days for both periods, but the graph displays 60 days, and on top of that, the values are incorrect.

How i did it : 

RelativeDays =

ADDCOLUMNS(

    GENERATESERIES(0, 60, 1),

    "DayLabel", "Day " & [Value]

)

Period1_Relative =
VAR Var1 = MIN('Table1'[Column1])

-- Relative offset from the axis (e.g. 0, 1, 2, ...)
VAR Var2 = MAX(Table2[Column2])
VAR Var3 = Var1 + Var2

RETURN
CALCULATE(
AVERAGE(Table3[Column3]),
FILTER(
Table3,
Table3[Column4] >= Var3 &&
Table3[Column4] < Var3 + 1
)
)

Period2_Relative = 

VAR Var1 = MAX('Table1'[Column1]) -- Dynamic axis table: 0, 1, 2...

VAR Var2 = MIN('Table2'[Column2])

-- Apply the offset
VAR Var3 = Var2 + Var1
VAR Var4 = Var3 + 1

-- Calculate the measure for the targeted time slot
VAR Result =
CALCULATE(
AVERAGE('Table3'[Column3]),
FILTER(
'Table3',
'Table3'[Column4] >= Var3 &&
'Table3'[Column4] < Var4
)
)

RETURN Result

Could you please help in that part? Thanks in advance for your help!

[v-sgandrathi · Accepted solution]

Hi @akim_no 

You're correct, the challenge you're now facing comes from the static nature of the RelativeDays table and how the measures are currently structured. Let's break it down and improve both aspects:

The axis should reflect only the number of days present in the selected periods. Instead of using a fixed GENERATESERIES(0, 60, 1) you can dynamically calculate the maximum number of days between the two periods and use that as the upper bound, or alternatively, filter your visuals/measures to show only valid days based on available data.

In your current measure, the offsets might be misaligned. Instead, consider this simplified logic for each period:

Period1_Relative = 
VAR StartDate1 = MIN('Period1Table'[Date])
VAR RelativeDay = SELECTEDVALUE('RelativeDays'[Value])
VAR TargetDate = StartDate1 + RelativeDay
RETURN
CALCULATE(
    AVERAGE('DataTable'[Value]),
    'DataTable'[Date] = TargetDate
)

This aligns each period from Day 0 onwards. Repeat the same for Period 2 with its respective start date.

To avoid extra blank space or irrelevant data on the axis:

• Use visual-level filters to show only days where data exists and ensure your DAX doesn't compute values beyond the selected range.

Happy to help! If this addressed your concern, marking it as "Accepted Solution" and giving us "kudos" would be valuable for others in the community.

Thank you.

[v-sgandrathi]

Hi  @akim_no ,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.

If you need any further assistance, feel free to reach out. 

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi · Accepted solution]

Hi @akim_no ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you.

[lbendlin]

You already describe the solution.  Make the X axis an integer, and create measures that compute the value for each of the periods for the xth day since the beginning of the period.