Forum Discussion
Dates improperly aggregating in visualization
A few things you need to fix.
1. My example of using a number (1-12) was an example if you have a Month column. Since you are using Month and Year, you need to make a unique order column for this value. I would make a calc column (query editor) that takes Year*100+Month. This will give you values like 201601 for Jan2016, 201602 for Feb2016 etc. Make sure the data type is numeric and this will allow you to sort correctly across multiple years.
2. You don't need to concantenate this unique sort order column with the Month Year column. Just select the field from the fields list, go to the Modeling tab, and choose Sort By > Unique Sort Order column.
Couple different options, one, create a hierarchy in your Axis by dragging in Year and then Month columns or create a new column that is essentially:
MonthYear = CONCATENATEX([Month],", ", [Year])
And use that as your Axis.
I couldn't see your image.
So does day/month/year all have to be in seperate columns? Right now the date is only in one column and reads 1/21/2017 as an example. When I drag in this date column Power BI seems to automatically break out the day/month/year which ultimately doubles up the month of Jan.
I dont know how I would create the hierarchy without first seperating the columns. Also I will look into your formula. DAX is new to me as well and still getting used to it. Not familiar with the forumlas yet.
Hi Viper1o34,
I am not able to view your image.
However, in your scenario, you can create the following columns, replace 'Date' table and 'Date'[DateKey] column with your owns in the DAX formulas below.
MonthNumber = MONTH('Date'[DateKey])
Year = YEAR('Date'[DateKey])
Month = SWITCH([MonthNumber],
1,"Jan",
2,"Feb",
3,"Mar",
4,"Apr",
5,"May",
6,"Jun",
7,"Jul",
8,"Aug",
9,"Sep",
10,"Oct",
11,"Nov",
12,"Dec",
"Invalid Month Number"
)
MonthYear = CONCATENATE('Date'[Month],CONCATENATE(",",'Date'[Year]))This way, you can put the MonthYear into Axis and get your expected result.
Thanks,
Lydia ZhangLydia,
Is there a video I can look at regarding the formulas? In your first formula example
MonthNumber = MONTH('Date'[DateKey])
I dont know what a "date key" is or how it should be used. Also when creating this there is not more than a single table. In the background data there is only one spreadsheet that contains the information I need. I do not have a seperate "Date" table that has a relationship with the ticket numbers I am counting. Do I need to create one?
In the forums I have seen many users referring to multiple tables. I come from using Tableau where I could simply drop and drag the data or write a simple formula.
Also is there a help number I can call here? There is nothing online where I can talk to a real person to troubleshoot small issues like this. If there is please PM me.
