Change month axis from rolling months to YTD on multiple graphs

[Jennifer_L_Mann]

I am working on building a dashboard with multiple graphs on the page displaying different metrics by month.

Is there a way to build a selector to change the Month axis from a rolling 13 months to year to date, on all of the visualizations at once? 

I would like to avoid having to manage bookmarks to make the switch as multiple pages will need this behavior. 

[v-sgandrathi]

Hi @Jennifer_L_Mann,

Thanks for the update, and great to hear that you were able to complete the first two steps successfully as @DataNinja777 mentioned. Regarding the issue you're facing in Step 3—where the IF(...) statements aren't letting you select your date field—it usually indicates that the model is not recognizing your Date column correctly in the context of the measure. This can happen if you’re either not using a proper Date table or if your Date table isn’t properly related to your data model.

To resolve this, ensure you have a dedicated Date table created using CALENDAR() or CALENDARAUTO() and that it is marked as a Date Table under the Modeling tab. Also, confirm that your visuals and calculations reference this Date table and not a date field from your fact table. Once that’s in place, your measure should recognize 'Date'[Date] as expected.

Glad I could assist! If this answer helped resolve your issue, please mark it as Accept as Solution and give us Kudos to guide others facing the same concern.

Thank you.

[v-sgandrathi]

Hi @Jennifer_L_Mann,

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.

[v-sgandrathi]

Hi @Jennifer_L_Mann,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[v-sgandrathi]

Hi @Jennifer_L_Mann,

As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?

And if the provided information meets your requirements, you can Accept the solution and also give Kudos on that reply. It helps other users who are searching for this same information and find the information.

Your understanding and patience will be appreciated.

[Jennifer_L_Mann]

I got pulled into another project and haven't been able to test, but will let you know when I get a chance!

[v-sgandrathi]

Hi @Jennifer_L_Mann,

Thanks for the update.
No worries we understand you're caught up with another project.
Please take your time. Just let us know once you get a chance to test it.

Thank you.

[v-sgandrathi]

Hi @DataNinja777, thank you for the detailed and accurate solution! Your explanation and steps for dynamically switching between "Rolling 13M" and "YTD" are correct and will help users achieve this functionality across all visualizations without the need for bookmarks.

To implement a dynamic toggle between "Rolling 13 Months" and "Year-to-Date (YTD)" across multiple graphs in Power BI, you can use a combination of a disconnected table and dynamic DAX measures. Here’s how you can achieve it:
Create a Disconnected Table: This will act as a slicer for switching between the two views.
Capture the Slicer Selection: Use DAX to capture the user's selection from the slicer.
Apply Date Filtering: Create a measure to filter your data based on the selected period (Rolling 13 Months or YTD).
Use the Flag in Visuals: Use the date filtering logic in your main measures to dynamically switch the data shown in your visuals.

This approach allows all visuals to update based on the slicer selection, providing a consistent user experience across the report. No need for bookmarks.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.

[DataNinja777 · Accepted solution]

Hi @Jennifer_L_Mann ,

To allow users to switch between a rolling 13-month view and a year-to-date (YTD) view across multiple graphs in Power BI without using bookmarks, you can create a disconnected table and use DAX to control the date range dynamically. This approach ensures consistency across visuals and works across pages.

Start by creating a disconnected table to act as a toggle. You can do this by going to the Modeling tab and entering the following DAX formula:

PeriodSelector = DATATABLE("Period Type", STRING, {{"Rolling 13M"}, {"YTD"}})

Next, create a measure to capture the selected value from the slicer:

SelectedPeriod = SELECTEDVALUE(PeriodSelector[Period Type], "Rolling 13M")

Then, create a measure that returns a flag to indicate whether each date should be included in the selected period range:

DateFilterFlag = 
VAR MaxDate = MAX('Date'[Date])
RETURN
SWITCH(
    [SelectedPeriod],
    "YTD", IF('Date'[Date] <= MaxDate && YEAR('Date'[Date]) = YEAR(MaxDate), 1, 0),
    "Rolling 13M", IF('Date'[Date] >= EDATE(MaxDate, -12) && 'Date'[Date] <= MaxDate, 1, 0),
    1
)

Now, use this flag to filter your main measure. For example:

Total Sales Dynamic = 
CALCULATE(
    [Total Sales], 
    FILTER('Date', [DateFilterFlag] = 1)
)

Finally, add a slicer to your report using the PeriodSelector[Period Type] column. This allows users to switch between "Rolling 13M" and "YTD," and the visuals will update accordingly based on the selected time frame.

Best regards

[Jennifer_L_Mann]

Thank you so much for these detailed steps. 

I am able to complete the first two steps without issue. However, when I try to create the flag, for the IF(... statements. It wont let me select my date field, it's only giving me my other created measures as options? Am I missing something?