Month and week on same x axis

[Tmedeiros]

So I looking for a solution to have a period of months and weeks on the same axis.

Example, show month Jan, Feb, Mar, but April didn't end and shows the weeks of the April until April is close and Carrie over the same logistic.

thank you

[C4YNelis]

Hi @Tmedeiros ,

if I understand correctly, you are looking for a way to start your axis with a category of one kind (month), but for the period that is not yet finished, you like to switch over to a category of a different kind (week).

The only way that I can think of to do this in an easy manner, is to edit your calendar table in Power Query by adding a column that does this calculation for you. So basically, in your calendar you add a column that will show you January on every row of January, February on every row that maps to February, March for every row that shows you March and after that, you switch to weeks. (so, for P1, you will show P1, for P2, P2, P3 = P3, but after that, your Power Query calculation should determine that this is the current period and switch to weeknumbers (14, 15, 16 up until the current week). You can then use this column as your X-axis and get the results you are looking for.

However, there is a potential downfall to this way of working, depending on how exactly you have defined your fiscal calendar, which is that, for a normal Gregorian calendar, weeks do not map nicely to months. However, it seems from your sample set that you define your months by full weeks. If that is the case, you can safely use the method above.

Cheers,

Niels

[Tmedeiros]

 

 

[v-shex-msft]

Hi @Tmedeiros,

Did you mean to show different level category on the same axis? If that is the case, I'd like to suggest you refer the following blog to unpivoted these fields, then you can use converted fields to create a chart with all level category at the same time.

Dynamic Attributes In A Power BI Report 

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @Tmedeiros,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@Tmedeiros , Create a new column in Date table 

if(month([Date]) < month(Today()), year([Date]) *100 + month([Date]) , year([Date]) *100 + weeknum([Date],2) )

You can create text column to using format and mark above as sort column

[Tmedeiros]

Good morning  @amitchandak ,

here a sample of the tables d_calendar and a f_transation. and a chart that I'm looking for to get the x axil with the period and weeks

 

thank you for the help

[Tmedeiros]

Thank you amitchandak 

My data is always in fiscal Year and Fiscal Week. I don't the days