Replacing Quarter label by selected period in X axis Graph

[Lilly768]

Hey team, 

I'm trying to do what it seems to be silly but i think it's not 

I have a graph showed by Quarter "Q1, Q2,..." and i would like to change it: 

instead of showing "Q1" i would like to show the month range for this quarter based on what the user selected in the date slicer.
Example 1: If the user selects the whole year, my bar graph would show 4 bars with the following labels (labels showing the month ranges for each quarter):

But it needs to be dynamic, so..
Example 2:  User selected February untill August on the dates slicer - I would like to see on the graph:

Basically i'm trying to group my data and avoid confusion with the users, so they won't think that Q1 contains data for all the Q1 months..

Is that a way to do what i want?

thanks a lot!

[OwenAuger · Accepted solution]

Hey @Lilly768 

This is an interesting one!

It seems to be one of those problems that's easier to describe than to solve 🙂

I have attached a dummy PBIX with some ideas.

It's late here, but my initial idea is that you would need to modify your data model and measures to get this to work:

1. Create a special table that contains a row for every possible "partial quarter". Let's call this 'Quarter Extended', screenshot below. It includes columns Quarter, Min Month, Max Month and Label, with Label being "Jan-Feb' etc.
2. Relate this to your 'Date' table via an intermediate 'Quarter' table containing the Quarters 1 to 4. The relationship between 'Quarter Extended' and 'Quarter' is bidirectional.
3. Create a helper measure called Display Flag, that return TRUE if the particular Label should be displayed (see below).
4. Modify existing measures (or use a calculation group) to only display measures when Display Flag = TRUE.
5. Create visuals using 'Quarter Extended'[Label] on the axis and the modified measures.

Quarter Extended table:

Data Model:

Display Flag measure

Display Flag = 
VAR QuarterMinMonthSelected =
    CALCULATE (
        MIN ( 'Date'[Month] ),
        VALUES ( 'Date'[Year-Quarter] ),
        ALLSELECTED ( 'Date' )
    )
VAR QuarterMaxMonthSelected =
    CALCULATE (
        MAX ( 'Date'[Month] ),
        VALUES ( 'Date'[Year-Quarter] ),
        ALLSELECTED ( 'Date' )
    )
VAR DisplayFlag =
    CALCULATE (
        NOT ISEMPTY ( 'Quarter Extended' ),
        KEEPFILTERS ( 'Quarter Extended'[Min Month] = QuarterMinMonthSelected ),
        KEEPFILTERS ( 'Quarter Extended'[Max Month] = QuarterMaxMonthSelected )
    )
RETURN
    DisplayFlag

Having done all this, you can filter on any range of months, and see the appropriate partial quarter labels on the axis of a visual:

This may well need refining, but I think the general idea of the 'Quarter Extended' table is probably the way to go.

Regards,

Owen

[Lilly768]

thank you Owen for your hard work on this!!

I will try it here 🙂