Chart showing totals by quarters and months of current quarter

[pmazz63]

Hi all,

I am new to Power BI and trying to figure out how to achieve this; I spent several hours on failed attempts and also searching posts with similar challenges but could not find anything applicable to my requirements.

I need a chart (based on data produced every month) to show totals by past quarters and also by any month for the current (not completed) quarter. I have a date table and managed to group current quarters (months outside these groups are ungrouped) but the chart can only be sorted either alphabetically or by ascending/descending number, not chronologically:

I think this is because the group with grouped quarters (Q1 and Q2) and ungrouped months (July and August) is seen as text, and I can't figure out how to concatenate the quarters and months to show chronologically.

This is my date table structure:

Any help will be greatly appreciated.

[Anonymous · Accepted solution]

For such a setting you have to create a separate static table with the pieces of time you need (just the names), have a column in there that would order them according to your liking and then have a separate measure that would respect selections in such a table. The table would be disconnected and you'd calculate the measure's values via virtual relationships. Basically, you can create any disconnected table you want with any attributes/fields and any hierarchies and then create measures that will work in conjunction with this table and the fact table even without any relationships between them.

[amitchandak]

@pmazz63 , this kind of display is not possible in power bi , You can take both on-axis and expand.

or create a table using summarize and union and use that for display. But table would be static in nature

[lbendlin]

What you can try is do a quarter/month hierarchy on the x axis, and then only expand the current quarter. Assuming your month names are sorted by a monthnumber column that should give you the desired outcome.

[pmazz63]

Thanks @lbendlin I tried to do that but I must be doing something wrong as when I expand / drill Q3 (current quarter) the previous quarters disappear:

[lbendlin]

Don't drill in, only right click on Q3 and say "Expand Selection" 

[pmazz63]

For some reason I can only see "Expand to next level"

[Anonymous]

@pmazz63 

To get precisely what you want you have to pay attention to this piece I wrote before:

Basically, you can create any disconnected table you want with any attributes/fields and any hierarchies and then create measures that will work in conjunction with this table and the fact table even without any relationships between them.

[pmazz63]

Thanks @Anonymous I will definitely look at the option you suggested.

[v-xuding-msft]

Hi @pmazz63 ,

Could you tell me if your problem has been solved? If yes, kindly accept the helpful answer as solution. And welcome to share your own solution. More people who encounter the same issue will benefit from it. If you are still confused about it, please share more details to us.