Forum Discussion
Order dates by current month
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.
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.
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.
