Order dates by current month

[sy898661]

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!

[edhans · Accepted 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:

• Go to the Modeling tab and press New Table. Type the formula below. Change the dates to suit your needs. SHould be a least the first and last date in your mode.

• 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.
• Now click on the data table icon (far left) in Power BI Desktop, and select your Calendar datable.
• Add a new column. Modeling tab, New Column. Type the below function.

• Month = MONTH('Calendar'[Date])

• Now go to the Relationships icon (again, far left of PBI window) and join the Date column in the Calendar table to the date column in your data table.
• Put the MOnth column in the bar chart for the months, and your data from your data table.

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.

[edhans]

Oh, 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.

[sy898661]

Thank you @edhans I really appreciate the help!! 🙂