Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sy898661
Helper V
Helper V

Order dates by current month

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. Cat Sad

 

Please help! 🙂

Thanks!

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.