Trendline in a Text date(Year_month) graph

[alexcatala]

Hi,

How can I add a trendline into a graph where the Axis are the dates(Year and month), but is established as text(unable to change it to date) and could add the trendline?

this is my calendar date:

d_DateTable =

VAR EarliestDate =

MIN ( MIN ( f_DailySales[Date] ), MIN ( f_Employees[EmpLeavDate] ) )

VAR LatestDate =

MAX ( MAX ( f_DailySales[Date] ), MAX ( f_Employees[EmpJoinDate] ) )

VAR StartDate =

DATE ( YEAR ( EarliestDate ), 1, 1 )

VAR EndDate =

DATE ( YEAR ( LatestDate ), MONTH ( LatestDate ) + 1, 1 ) - 1

RETURN

ADDCOLUMNS (

CALENDAR ( StartDate, EndDate ),

"Year", YEAR ( [Date] ),

"Quarter", QUARTER ( [Date] ),

"Month", MONTH ( [Date] ),

"Week", WEEKNUM ( [Date], 1 ),

"Year_Quarter", YEAR ( [Date] ) & "_Q"

& QUARTER ( [Date] ),

"Year_Month", YEAR ( [Date] ) & "_M"

& FORMAT ( MONTH ( [Date] ), "00" ),

"Year_WeekNum", YEAR ( [Date] ) & "_W"

& FORMAT ( WEEKNUM ( [Date], 1 ), "00" ),

"Day_Week",WEEKDAY([Date],2),

"Year_WeekNum (Skechers)",

VAR SkWeek =

LOOKUPVALUE ( d_SkechersWeeks[Skechers Week], d_SkechersWeeks[Date], [Date] )

VAR LastSkDay =

MAX ( d_SkechersWeeks[Date] )

VAR WeekNum_Bias =

VALUE (

RIGHT (

LOOKUPVALUE ( d_SkechersWeeks[Skechers Week], d_SkechersWeeks[Date], LastSkDay ),

1

)

)

- WEEKNUM ( LastSkDay, 1 ) //-1

VAR AdjustedWeekNum =

YEAR ( [Date] ) & "_W"

& FORMAT ( WEEKNUM ( [Date], 1 ) + WeekNum_Bias, "00" )

RETURN

IF ( [Date] > LastSkDay, AdjustedWeekNum, SkWeek )

)

As I want to obtain the values from each month and year, I created the date(Year_month) to could minimise the amount of data, but due as it is established as Text cant add a trendline.

Any suggestion?

Thanks

[Anonymous]

Hi @alexcatala ,

It seems like as long as the type of Year-month column is Date, it could be solved...

How about copying the original Date column and format it as yyyy-mm  directly in Column Tools tab like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[amitchandak]

@alexcatala , In case you need a continuous line, you have to plot it on the date.  Maybe you can try on YYYYMM (Data type Int)

[alexcatala]

@amitchandak 

Do you mean change the current Year_month? or make an extra one?

If so what would you think it will be the best formula.

"Year_Month", YEAR ( [Date] ) & "_M"

& FORMAT ( MONTH ( [Date] ), "00" ),

Thanks

[alexcatala]

@v-lionel-msft 

Do you mind to have a look at this issue?

As mentioned above I cant add a trendline as the date I used as the axis is with the format (TEXT). I use Year and month, but cant is saved as date as it is not valid. 

To be able to add a trendline it has to change the x type from categorical into continuous. Unfortunately, it pops this message and can't add the continuous type.

Any suggestion?

Thanks for your time!

[alexcatala]

@v-lionel-msft