Viewing months based on current date on charts

[Colleen]

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. 

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

Thanks in advance,

Colleen

[pbeye · Accepted solution]

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:

[pbeye · Accepted solution]

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:

[Colleen]

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

[pbeye]

No problem @Colleen  Glad to help! 

[Colleen]

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:

Graph 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]

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]

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:

Thank you so much for your continued help!

Kind regards,

Colleen

[pbeye · Accepted solution]

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. 

[Colleen]

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