cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Colleen Helper I
Helper I

Viewing months based on current date on charts

Hello, 

 

How can I change the order and view of months on the x-axis on a graph?

 

I need to display financial YTD sales information. I have used the time intelligence functions to create a new column to display this for me which works as I wish. However, when I come to display this information it starts from January and extends to December (which as this hasn't occured leaves a plateau in the graph). 

 

Either I need to display from april (the start of our financial year) to the current month, or from last year to the current month. E.g as it is august 2019, the x-axis should run from September-18, to August-19. Next month that would then change accordingly.

 

I am new to Power BI so please as much information as possible would be fab. 

image.pngIdeally this graph would run from Sep-18 (which you cannot see from this view) to Aug-19

 

Thanks in advance,

Colleen

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
pbeye Resolver II
Resolver II

Re: Viewing months based on current date on charts

Here's how I would do it:

 

  1. Make a copy of your date column getting the start of the month: startofmonth = STARTOFMONTH(dates[yourdatecolumnhere])
     
  2. Change the format of that column to March 2001 in the Modeling tab.
  3. Add to your chart and make sure the X axis is listed as categorical. 

 

You should end up with something like this:

Capture.PNG

View solution in original post

pbeye Resolver II
Resolver II

Re: Viewing months based on current date on charts

Ah, I see. It's because of your measure formula. Here's what I think are your options:

 

  1. You can remove your measure and replace it with the LineTotExcl field instead. That will include the missing months, but will make your chart start at January.
  2. I think in order to get the graph to start at April, you have to create a custom date/calendar table to create your fiscal year. I'm not familiar with how to do that, but I've read about others having to do this before. I won't be able to help you on that part, unfortunately. 

 

 

View solution in original post

8 REPLIES 8
Highlighted
pbeye Resolver II
Resolver II

Re: Viewing months based on current date on charts

Here's how I would do it:

 

  1. Make a copy of your date column getting the start of the month: startofmonth = STARTOFMONTH(dates[yourdatecolumnhere])
     
  2. Change the format of that column to March 2001 in the Modeling tab.
  3. Add to your chart and make sure the X axis is listed as categorical. 

 

You should end up with something like this:

Capture.PNG

View solution in original post

Colleen Helper I
Helper I

Re: Viewing months based on current date on charts

Thank you so much @pbeye. You've completely solved my problem 🙂

pbeye Resolver II
Resolver II

Re: Viewing months based on current date on charts

No problem @Colleen  Smiley Very Happy Glad to help! 

Colleen Helper I
Helper I

Re: Viewing months based on current date on charts

I admit I missed something sorry @pbeye !

 

Having returned a bit more fresh faced this monday I've realised my graph only spans from April to December as shown:

 

image.pngGraph runs April to December rather than 12 months prior

 

The display worked fine for when I was only looking at this year as it ran April to July (most recent month with data).

 

Please what do I need to do for mine to run the full 12 months until March (the end of our financial year)? I've followed the steps exactly with this outcome.

 

Kind regards,

Colleen

 

pbeye Resolver II
Resolver II

Re: Viewing months based on current date on charts

Hi @Colleen ,

 

I suspect it has something to do with your filters or your fiscal year formula. Can you provide an example for each of those?

Colleen Helper I
Helper I

Re: Viewing months based on current date on charts

Hi @pbeye ,

 

This is how I calculate my YTD values

Sales YTD Fiscal = TOTALYTD(
                SUM('Raw Data'[LineTotExcl]),
                'Raw Data'[TxDate].[Date],
                "31/3"
)

And here are the filters and data values on the visual:image.png

 

Thank you so much for your continued help!

 

Kind regards,

Colleen

 

 

 

 

pbeye Resolver II
Resolver II

Re: Viewing months based on current date on charts

Ah, I see. It's because of your measure formula. Here's what I think are your options:

 

  1. You can remove your measure and replace it with the LineTotExcl field instead. That will include the missing months, but will make your chart start at January.
  2. I think in order to get the graph to start at April, you have to create a custom date/calendar table to create your fiscal year. I'm not familiar with how to do that, but I've read about others having to do this before. I won't be able to help you on that part, unfortunately. 

 

 

View solution in original post

Colleen Helper I
Helper I

Re: Viewing months based on current date on charts

Ah thank you for that clafication.

 

I've seen online about making custom calendars and It looked very tricky to understand, but I guess that will have to be that way! Time to do some scraping. Cheers

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors