Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
HarryH
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
CheenuSing
Community Champion
Community Champion

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!

View solution in original post

5 REPLIES 5
CheenuSing
Community Champion
Community Champion

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!

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.

 

Problem is when I am adding there measure from other spreadsheet.

 

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.

I have date table which links both spreadsheets.

 

Any idea how to sort this out?

 

Many thanks in advance.

 

Kind regards.

 

Andrej

Hello @CheenuSing

 

Your tip was excelent.

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

 

 

 

2016 & 2017.JPG

Hi @AndrejZitnay

 

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!

Hello @CheenuSing,

 

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

 

 

Very Usefull.

 

Thanks.

 

Andrej

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors