Trend line when x-axis is text field

[earlenereid]

I would like to add a trendline to a stacked column chart.  The x-axis is a text field which displays the year/month formatted as YYYY-MM.  I am able to add a Constant line, but not a trend line.  Example shown below.

This is simple to do in Excel, but I have not found a way to do it in Power BI.  Any help would be much appreciated!

[Eric_Zhang]

---

@earlenereid wrote:

I would like to add a trendline to a stacked column chart.  The x-axis is a text field which displays the year/month formatted as YYYY-MM.  I am able to add a Constant line, but not a trend line.  Example shown below.

This is simple to do in Excel, but I have not found a way to do it in Power BI.  Any help would be much appreciated!

---

@earlenereid

You might need a calendar table as below

calendar =
ADDCOLUMNS (
    CALENDAR ( "2017-01-01", "2017-12-31" ),
    "Year-Mon", CONCATENATE (
        CONCATENATE ( YEAR ( [Date] ), "-" ),
        RIGHT ( CONCATENATE ( "0", MONTH ( [Date] ) ), 2 )
    )
)

The a measure as below to calculate the "trend"

diff =
VAR currentVal =
    SUM ( 'Table'[value] )
VAR preMonVal =
    CALCULATE ( SUM ( 'Table'[value] ), PREVIOUSMONTH ( 'calendar'[Date] ) )
RETURN
    ( currentVal - preMonVal )
        / currentVal

See more details in the attached pbix.

[mdannemiller · Accepted solution]

It doesnt appear that you can because power bi doesnt know how to order them chronologically.  I'd make another column which creates a date from that column 

NewDate = DATE(LEFT([Date],4), RIGHT([Date],2), 1) or similar which will give you a date which is the first day of each month. 

[earlenereid]

I was able to create a date column to be used as my x-axis column.  With a few additional adjustments, I can now add a trend line:

• Change x-axis type from Categorical to Continuous
• Change from Stacked Column chart to Clustered Column

There is a remaining issue:

My x-axis is only showing labels on every other month.  How do I control that?