DAX Trend Line with Month Year column

[yaman123]

Hi, 

Is there a way to add a trend line using a month year column (mmmm yyyy).  The Month Year column is calculated from the SAMPLE DATE column.

The below is the query to calculate the trend - 

Linear regression =

VAR Known =

FILTER (

SELECTCOLUMNS (

ALLSELECTED ( Query1[SAMPLE_DATE] ),

"Known[X]", Query1[SAMPLE_DATE],

"Known[Y]", [Count of Member Code]

),

AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )

)

VAR Count_Items =

COUNTROWS ( Known )

VAR Sum_X =

SUMX ( Known, Known[X] )

VAR Sum_X2 =

SUMX ( Known, Known[X] * Known[X] )

VAR Sum_Y =

SUMX ( Known, Known[Y] )

VAR Sum_XY =

SUMX ( Known, Known[X] * Known[Y] )

VAR Average_X =

AVERAGEX ( Known, Known[X] )

VAR Average_Y =

AVERAGEX ( Known, Known[Y] )

VAR Slope =

DIVIDE (

Count_Items * Sum_XY - Sum_X * Sum_Y,

Count_Items * Sum_X2 - Sum_X * Sum_X

)

VAR Intercept = Average_Y - Slope * Average_X

RETURN

SUMX ( DISTINCT ( Query1[SAMPLE_DATE]), Intercept + Slope * 'Query1'[SAMPLE_DATE])

But i get the below results when i use the Line and Clustered Column Chart visual. 

Thanks

[Icey · Accepted solution]

Hi @yaman123 ,

This post will help you: Use Custom DAX to create a Linear Regression Trendline with a Dynamic Date Hierarchy in Power BI - M....

It create a trend line by week-year. And here is the corresponding video:

Use Custom DAX to create a Linear Regression Trendline with a Dynamic Date Hierarchy in Power BI - Y....

Best Regards,

Icey

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

[johnt75]

Set up a date table linked to the sample date column and then in your measure definition replace every mention of the sample date column with the column from your date table which you are using in the visual - e.g. 'Date'[Year month].

Its important that the column used in the trend line measure matches exactly with the column you are using as the axis

[yaman123]

hI @johnt75 

I get the below error when changing the measure definitions and adding the new column as the X axis? 

My new measure - 

Linear regression =

VAR Known =

FILTER (

SELECTCOLUMNS (

ALLSELECTED ( 'Date Table'[Date Month Year] ),

"Known[X]", 'Date Table'[Date Month Year],

"Known[Y]", [Count of Member Code]

),

AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )

)

VAR Count_Items =

COUNTROWS ( Known )

VAR Sum_X =

SUMX ( Known, Known[X] )

VAR Sum_X2 =

SUMX ( Known, Known[X] * Known[X] )

VAR Sum_Y =

SUMX ( Known, Known[Y] )

VAR Sum_XY =

SUMX ( Known, Known[X] * Known[Y] )

VAR Average_X =

AVERAGEX ( Known, Known[X] )

VAR Average_Y =

AVERAGEX ( Known, Known[Y] )

VAR Slope =

DIVIDE (

Count_Items * Sum_XY - Sum_X * Sum_Y,

Count_Items * Sum_X2 - Sum_X * Sum_X

)

VAR Intercept = Average_Y - Slope * Average_X

RETURN

SUMX ( DISTINCT ( 'Date Table'[Date Month Year]), Intercept + Slope * 'Date Table'[Date Month Year])

[johnt75]

Try changing the Date Month Year column to be of type date. If you store it as 01/mm/yyyy then you can set the format string to display just month and year, it should give the results you're looking for

[yaman123]

Still not giving me the results unfortunately 

Date Month column is - 

Date Month Year = FORMAT('Date Table'[Date], "mmmm yyyy")

Data Type is Date and Format to Month Year

Linear regression =

VAR Known =

FILTER (

SELECTCOLUMNS (

ALLSELECTED ( 'Date Table'[Date Month Year] ),

"Known[X]", 'Date Table'[Date Month Year] ,

"Known[Y]", [Count of Member Code]

),

AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )

)

VAR Count_Items =

COUNTROWS ( Known )

VAR Sum_X =

SUMX ( Known, Known[X] )

VAR Sum_X2 =

SUMX ( Known, Known[X] * Known[X] )

VAR Sum_Y =

SUMX ( Known, Known[Y] )

VAR Sum_XY =

SUMX ( Known, Known[X] * Known[Y] )

VAR Average_X =

AVERAGEX ( Known, Known[X] )

VAR Average_Y =

AVERAGEX ( Known, Known[Y] )

VAR Slope =

DIVIDE (

Count_Items * Sum_XY - Sum_X * Sum_Y,

Count_Items * Sum_X2 - Sum_X * Sum_X

)

VAR Intercept = Average_Y - Slope * Average_X

RETURN

SUMX ( DISTINCT ( 'Date Table'[Date Month Year]), Intercept + Slope * 'Date Table'[Date Month Year])

[johnt75]

The only other thing I can suggest is trying

Date Month Year = DATE( YEAR('Date Table'[Date]), MONTH('Date Table'[Date]), 1)

Other than that, your code appears to be the same as what I have used for trend lines in the past.