Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to Solution.
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
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
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
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
98 | |
96 | |
38 | |
36 |
User | Count |
---|---|
151 | |
125 | |
75 | |
74 | |
53 |