cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sglendenning
Frequent Visitor

Using YYYYMM as date axis not displaying right

I have a date table and added a column in the date table to get the YYYYMM format (YYYYMM Test 2 = FORMAT([Date],"YYYYMM").  I changed the data type to whole number and used it on the x axis changing the x-axis type to continuous.  What I get is the graph below where the long flat lines are the in between values from 201912 to 202001.  The program is essentially trying to fill in the number gap.  If I format as text it won't let me use continuous in the x-axis and doesn't sort the axis sequentially (or I'm not using the right sort within the date table because I can't sort by [Date] becuase then I get an error that says I have multiple entries in the YYYYMM format for one entry of [Date].

 

I'm ultimately trying to get this figured out so I can add trend lines to my graph and can't do that with the categorical aspect of Calendar Year and Calendar Month.  My data is daily but I want to aggregate by month.  The data values are from a created measure that is for sales Sales = SUM([Sales Count])

 

sglendenning_0-1631893617942.png

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @sglendenning ,

 

I just add trend lines like so. Can this meet your requirements?

Icey_0-1632208219095.png

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
maguilarBI
Resolver II
Resolver II

This usually ocurrs beacuse have no date in various periods. You can use the categorical type for X axis. However, it's convenient you explore the data, maybe the data is concentrated in a few months, then if you use categorical type for X axis, you will only have one or couple of months for year, in this case, you could present the data annually.

Thank you but I'm trying to get trend lines on the graph and in order to do this I need a continuous axis format from what I have been reading.  I have a date heirarchy for Calendar Year and Month that works out great for the graphing but I don't get the option to include a trend line.  I need a continuous field on the x-axis but when using the YYYYMM format I have created I get the weird graphing but do get the trending.

vanessafvg
Super User
Super User

can you share some data and what is it you are expecting to see?  your Dates look funny,  201950?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I think Power BI is trying to fill in linear gaps of my dates so it is showing 201950 along the axis but only because it is between 201912 and 202001 (I can confirm there is no data nor field in my date table that contains 201950).  I'm only using the format YYYYMM in order to get a continuous axis in order to be able to get the analytics trend line.  If I use my date table format where I have a heirarchy that includes Calendar Year and Calendar Month everything works out great...but the option for a trend line disappears due to the categorical aspect of the date axis now.  What I have read is that I need a continuous column such as YYYYMM and that allows me to get the trend lines but messes up the graphs.

what were you expecting your data to look like?  can you share some dummy data and an example of what you are expecting?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I'm trying to get the graph to look like the one below...but with trend lines.

 

sglendenning_0-1631907139128.png

 

Icey
Community Support
Community Support

Hi @sglendenning ,

 

I just add trend lines like so. Can this meet your requirements?

Icey_0-1632208219095.png

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!