Forum Discussion

yaman123's avatar
yaman123
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

DAX Trend Line with Month Year column

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

 
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

6 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      yaman123
      Icon for Post Partisan rankPost Partisan
      4 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        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

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi yaman123 ,

     

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

     

    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 - YouTube.

     

     

    Best Regards,

    Icey

     

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