Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I have a table that contains the following columns [Date of Request, Request Description], and I am trying to create a column chart with Number of Requests per Month.
The dates range from August 2017 to July 2018, but when I put this data into a graph, it is organized Jan-Dec as if all of the data were in the same year (if that makes sense?)
I was wondering what a solution would be to organize the data correctly so it doesnt look like I only have data for the year of 2018. The way my excel table was set up does not separate day/month/year (instead, it just shows the date of the request like 8/15/18) so there is not a separate column for Month, or else I would add in an order number column and sort it by that.
I've never used DAX or anything like that so there might be a simple solution, I just don't know it!
Also, I would prefer not to have to make another table like [Month, Number of Requests] because creating a relationship between that and my main table has nottt worked out for me so far.
Please help! 🙂
Thanks!
Solved! Go to Solution.
To do it correctly, you really do need a date table. You always should have a date table in your model if you want to do anything by date, like a chart by months.
To create a simple table, that should work for you:
Calendar = CALENDAR(DATE(2017,1,1), DATE(2018,12,31)You may want to format it as a pure date from the modeling tab, Formatting section. By default it has date and time.
Month = MONTH('Calendar'[Date])
There is more if you want something like Jan, Feb, Mar, or January, February, March, etc. but that should get you started. For example:
Short Month Name = FORMAT('Calendar'[Date],"MMM")
would be the column to add to the date table to have Jan, Feb, Mar available.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingTo do it correctly, you really do need a date table. You always should have a date table in your model if you want to do anything by date, like a chart by months.
To create a simple table, that should work for you:
Calendar = CALENDAR(DATE(2017,1,1), DATE(2018,12,31)You may want to format it as a pure date from the modeling tab, Formatting section. By default it has date and time.
Month = MONTH('Calendar'[Date])
There is more if you want something like Jan, Feb, Mar, or January, February, March, etc. but that should get you started. For example:
Short Month Name = FORMAT('Calendar'[Date],"MMM")
would be the column to add to the date table to have Jan, Feb, Mar available.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOh, one other thing. For your needs above a quick and dirty date table like I laid out will work, but to really do it the best way, you should do it in Power Query and bring it into the model. See this article. I have something similar (more dynamic, but same concept) that I always just paste into any new report I work on.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |