April Update - Trend Lines

[cmfisher82]

Hi All,

I know the ability to add trend lines was just added in the April update.  However, I'm really confused on how exactly to use them.

Basically, I've got a column bar chart showing the number of items opened every week of the year.  I'd like to add a trend line to this chart.  However, the option for trend line is not available on the column bar chart for some reason.  I've tried changing the chart types and the only one where I see the option for a trend line is the scatter chart and even then the trend line option won't actually turn on (not to mention a scatter chart is useless for my application).

Has anyone had this experience or found out how to work this?  I'm really confused on something that seems like it should be extremely simple.

Thanks in advance.

[Seth_C_Bauer]

@cmfisher82 Did you see this post? Could what you are experiencing fall into what is discussed there, or is this different?

[cmfisher82]

Thanks - I did not see that post.

Good news and bad news though - changing to continuous x-axis worked in adding the trend line, but unfortunately this removes a lot of the week number labels at the bottom of my bar chart.  Obviously, this makes the chart much less readable/functional at a glance.  Are you aware of a way to add the labels back to each bar along the x-axis?

[Seth_C_Bauer]

@cmfisher82 yeah, that's odd. I tested out months in a year and the best I got was every other column even though there is plenty of space to put the x-axis value... But that seems to be across visuals regardless of whether or not I'm using the trend. I am not aware of a way to force it to show all values on the axis. 😞

[cmfisher82]

@Seth_C_Bauer - Thanks for trying.  I'll leave this up here to see if anyone else has ideas.

On a related, but unrelated to this specific topic question - I currently have to create separate bar charts for the number of items opened by week number and the number of items closed by week number.  Obviously these share a common x-axis (week number), but the values for opened/closed should be unique for each week. 

When I try to combine these into a single bar chart using a clustered column chart, the count does not work correctly for closed.  Every week matches the count of open.  For example, let's say I have 156 items opened in week 1 and 86 items closed in week 1.  If I create separate charts, this works fine.  However, if I try to combine this into a single chart, I get 156 opened and 156 closed (rather than 86 closed as it should be.

Any idea on what could be happening here or how to fix it?  Sorry, I know I'm a bit off topic to my original post.

[Seth_C_Bauer]

@cmfisher82 There are several things that could be affecting the output, the relationships in the model, how you are using things in the visual...

Are you using two different counts? Each count as it's own measure?

What does the relationship look like in the model, is this all one table, are these counts in the same table and do they relate to the same date table?

Some of the above may be off-base, but suffice to say, I need a little bit more info on what your data looks like before I can try to figure out the issue.

[cmfisher82]

@Seth_C_Bauer - I have two separate data extracts - one for items opened in the last 12 months and another for items closed in the last 12 months.  In each dataset, I've wrote a DAX function to extract the week number.  I have no relationship defined between week opened / week closed)(if I try, it says a relationship cannot be established because one column must have unique entries).

From the extracted week number, I have a clustered column chart.  The axis for each column chart is week opened (or week closed) and the value is count of week opened (or count of week closed).

Now to combine the charts, the axis has to be either week opened or week closed.  I've tried adding both count of week opened AND count of week closed in the value column, but that is what yields the incorrect information.  I haven't been able to find a way to accurately show count of week opened and cound of week closed in the same chart.

Hopefully that makes sense.

[Seth_C_Bauer]

@cmfisher82 Yep, makes sense. The issue you are experiancing are because you don't have any relationship. You could probably write up some Dax query to reference the other table, but a more straightforward way is to just create a date table. In this way, you can slice your information in whatever time slice you want.

Here is a simple blog I've pointed others too, there are many different ways to create date tables, so feel free to look for others if you don't like what you see. Essentially, you need one date to relate to both your tables, so the counts return correctly.

Build this table, create relationships between this table and both your opened and closed info, use the date/week from the new date table as your axis, and both counts should return as expected. 

Side note, you may need to apply your dax week function to this table as well to get the Week Number. - take a look.