Linear Regression with Date Hierarchy on X Axis

[rqh]

Hi everyone!

I have a line chart with average shipping costs (SC/O) as my y-axis and Calendar Quarter / Month Name as my x-axis.

[Calendar Quarter] and [Month Name] are a part of a date hierarchy created from my 'Date' table.

I have managed to create a line regression measure LINESTX using the [Order Date] column on my 'Orders' fact table. My problem with it is the line chart is becomes too granular (plots out every single date an order is placed). I would like the date to be rolled up, like by month or quarter.

I can't seem to get LINESTX to function properly when using the date hierarchy instead. Any tips?
Date tableOrders table (fact)The linear regression measure works but it doesn't when I use my date hierarchyDAX for linear regression (thanks SQLBI)

[OwenAuger]

Hi @rqh 

If you are happy to visualize the results of original daily linear regression at any level of the 'Date' hierarchy, you could leave the measure unchanged except for the variable x which you could redefine as follows, depending on which date you want to select from the date range corresponding to each data point:

VAR x = MAX ( 'Date'[Date] )
-- or --
VAR x = AVERAGE ( 'Date'[Date] )
-- or --
VAR X = MIN ( 'Date'[Date] )

 As long as you use columns of the 'Date'table on the x-axis of the visual, this should work fine.

Does something like the above work?

[rqh]

Hi Owen,

I think we're getting close! By using your suggestions I am able to use my date hierarchy. The problem now is that, the linear regression line itself is drifting too high on the graph. By intuition. anyone can tell that the line is not the best fit line.

If you want more screenshots (or if you want my .pbix file, my dataset is fake), let me know. Thanks!This is my LINESTX table, the measure Linear Regression uses the column outputs from this table.The new measure, per your suggestion

[OwenAuger]

Thanks for the update @rqh  🙂

The PBIX would be useful if you can share it (OneDrive link or similar).

I do want to make sure the calcs are working as I think they are.

One question: Would you be happy if the regression line recalculates depending on the granularity of the axis, in order to fit the datapoints displayed?