Forum Discussion
Building a column chart with multiple years
Hello,
I want to build a bar chart that shows the month and the year, because I have entries in my data that are further in the future than a year.
background: I am building a dashboard to show our investments, and we enter data into a spreadsheet that has the maturity date, as well as the amount that is maturing.
The three fields I am using are: amount, maturity date, and days remaining (this is the time from today to the maturity date)
The days remaining will always be filtered for greater than 0 since we don't care about looking at things we no longer hold.
I select stacked column chart, enter amount and maturity, then throw in the days remaining filter for greater than 0. I then remove the date hierarchy to show month only but it shows in the order of Jan through December rather than the first month being the current month (as of now May)
How can I set this up so that it always shows the current month first, and shows months into the next year?
Thanks for your help
3 Replies
Anonymous
Add a column to your Dates table that gives you the first day of the month for every day in that month.
Month Year = EOMONTH([Date],-1)+1Then format that using the string "mmmm"
It will look weird in the Dates table, like "MJul" but on the chart it will be correct.
Add the new filed as your X axis and set it to Categorical. It displays only the month name but, in the background it is just formatting 7/1/2019 and 7/1/2020 as July, that is why July appears twice and in the correct order.
By "Dates Table" do you mean a seperate table that just is basically just a calender? My file does not have one of those currently, the only instances of dates are a column "date purchased" and "maturity date". I'm really quite green to power BI so forgive me if I ask questions that seem obvious. Do I need a Dates Table if I have these 2 existing columns?
Normally you want to have a dedicated calendar table in your model and you would link that to your date filed in the fact tables. You could just add the code above as a calculated column in your existing table and use it straight from there though.
