Forum Discussion
Month and week on same x axis
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
7 Replies
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
Super User
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
Thank you amitchandak
My data is always in fiscal Year and Fiscal Week. I don't the days
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
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
C4YNelisAdvocate III
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
