cancel
Showing results for
Did you mean:
Regular Visitor

How to show 2 years on x axis of bar chart when displaying months only?

Currently my data set has random dates between 01/01/2016 - 09/05/2017.

I'd like to show the count of dates per month spanning from the beginning of 2016 - present 2017, at the moment the count for months from 2017 are combining with those from 2016 and only forming 12 months along the x axis, rather than 17 months including the additional 5 from 2017.

Can anybody help?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

Re: How to show 2 years on x axis of bar chart when displaying months only?

Hi @HarryH

Try out the following

1. Create a column called YearMonthNumber as

YearMonthNumber = (Year('Sales'[Date])) * 12 + Month( 'Sales'[Date])

2. Create a column called MonthYear as

MonthYear = Format(Sales[Date],"mmm") & "-" & Format(Sales[Date],"yyyy")

3. Set the Sort by Coulmn for MonthYear to  YearMonthNumber

4.  Create a measure called DaysIntheMonth = DistinctCount(Sales[Date])

5. Now plot the barchart

with MonthYear as Axis and DaysIntheMonth as values.

6. You should get the Axis displayed for 17 months .

If this solves your issue please accept this as  a solution and also give KUDOS.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
5 REPLIES 5
Super User I

Re: How to show 2 years on x axis of bar chart when displaying months only?

Hi @HarryH

Try out the following

1. Create a column called YearMonthNumber as

YearMonthNumber = (Year('Sales'[Date])) * 12 + Month( 'Sales'[Date])

2. Create a column called MonthYear as

MonthYear = Format(Sales[Date],"mmm") & "-" & Format(Sales[Date],"yyyy")

3. Set the Sort by Coulmn for MonthYear to  YearMonthNumber

4.  Create a measure called DaysIntheMonth = DistinctCount(Sales[Date])

5. Now plot the barchart

with MonthYear as Axis and DaysIntheMonth as values.

6. You should get the Axis displayed for 17 months .

If this solves your issue please accept this as  a solution and also give KUDOS.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Helper V

Re: How to show 2 years on x axis of bar chart when displaying months only?

Hello @CheenuSing

I am facing now same issue.

I have still ony problem with sorting.

What I am doing wrongly?

I have MonthYear in Axis & me % rate in Values

Thx

Super User I

Re: How to show 2 years on x axis of bar chart when displaying months only?

Refer to point 3.

3.  Set the Sort by Coulmn for MonthYear to  YearMonthNumber.

This should resolve displaying the MonthYear in the correct order.

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Helper V

Re: How to show 2 years on x axis of bar chart when displaying months only?

Hello @CheenuSing,

I didn't know how to do taht but now it is fine.

Very Usefull.

Thanks.

Andrej

Helper V

Re: How to show 2 years on x axis of bar chart when displaying months only?

Hello @CheenuSing,

Sorry to be a pain.

I have one extra question.

I have created my new charts and they are excellent with data from my spreadsheet.

For example I might have list of 100 lines.

30 renewals & 70 settlements over 19 months.

I might have another excel with 200 lines.

I have measure 200-30 = 170

170 is conquest.

I can't visual conquest with renewals in my new chart.

I can visual them only in normal 12 month chart by date.

Any idea how to sort this out?

Kind regards.

Andrej

Announcements

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors