DAX to create a Trend line?

ECE 

I'm sorry, this is a very late reply to your reply 😓

If you are dealing with YearMonth as a text column, I would sugget adding a YearMonth index to your Date table and using that instead.

To illustrate in DAX, the formula would be something like:

YearMonth Index =
YEAR ( 'Date'[Date] ) * 12 + MONTH ( 'Date'[Date] ) - 1

The reason for subtracting one is that the Year or Month can be recovered from the Index using integer division or the modulo operator.

This index increases by one for each month, so can be used in the regression calculation.

Regards,

Owen

Hello OwenAuger, I wonder if I could get a little help here on what surely seems to be an easy situation but confounding me nonetheless.

I'm looking to create a column in one table with the slope of trended metrics from another. 

• One table (on the right) is current values of providers and associated metrics which I use for all sorts of things.  I'd like to add  a column with the slope of the trend for each provider/metric combination.  There are ~600 metrics for each of ~4000 hospitals to compute a slope for.
• The other table (on the left) is a table of trend values...of these provider/metric combinations.  So it contains the same ~600 metrics over as many as 8 years for the same ~4000 hospitals. 
• Each table has a Variable_Name and Provdrno.  I want to use the Provdrno AND Variable name from the row in the Right Table to give me the selection of trend values in the LEFT Table.  Seems eacy enough

• So I've read what seem to be the relevant posts....and tried a variety of attacks.  Here's the one that does seem promising.

TempSlope = 
VAR RowMetric = SelectedValue(HospMeasures[Variable_Name])
VAR RowProviderID = SelectedValue(HospMeasures[PROVDRNO])

VAR Known =
        SELECTCOLUMNS (
            CALCULATETABLE ( ALL(TR_MeasureTrends),  TR_MeasureTrends[PROVDRNO] = RowProviderID , Tr_MeasureTrends[Variable_Name] = RowMetric 
			),
            "Known[X]", Tr_MeasureTrends[FY_Trend] ,
            "Known[Y]", Tr_MeasureTrends[Variable_Value]
        )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, VALUE(Known[X]) )
VAR Sum_X2 =
    SUMX ( Known, VALUE(Known[X]) ^ 2 )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, VALUE(Known[X]) * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, VALUE(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 ^ 2
    )
RETURN
Slope

Unfortunately every row has the same value!  ugggg.....It's very mysterious.

I believe the codes does the following

• creates VAR's for the relevant row info (ProvdrNo and Variable_Name)
• Select columns from the trend table where I
  • clear all filters at the row level,
  • filter for the proper provdrno and variable_name
  • select the two columns I need for the slope calculation
  • convert the FY_date column to a number as required.
  • Do the calcs.

Yet every value is identical.  As I thrashed around, the table name only did not yield any values, ALLSELECTED in any form did not yield any values (Yes, that was crazy)....so I'm stumped.

Suggestions?  Thank you in advance,

Tom

Hi matthewtay 

Thanks for your patience as I have been a bit busy this month!

I have attached a PBIX containing a suggested approach.

1. With the LINEST/LINESTX functions now available, I suggest using these rather than the calculation in the post above. For your example, I would use LINESTX.
2. To get this to work, you will need Percentage to be a measure. That's why it wasn't appearing in the intellisense options.

Here are the measure definitions in the attached PBIX:

Percentage = 
DIVIDE (
    SUM ( data[Abnormal Results] ),
    SUM ( data[Workers tested] )
)

Trendline = 
VAR Known =
    FILTER ( 
        SELECTCOLUMNS (
            CALCULATETABLE ( VALUES ( 'data'[Date of test] ), ALLSELECTED ( 'data' ) ),
            "Known[X]", 'data'[Date of test],
            "Known[Y]", [Percentage]
        ),
        AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
    )
-- Regression using LINESTX function rather than explicit calculation
VAR Regression =
    LINESTX (
        Known,
        Known[Y],
        Known[X]
    )
VAR Intercept =
    SELECTCOLUMNS ( Regression, "@Intercept", [Intercept] )
VAR Slope =
    SELECTCOLUMNS ( Regression, "@Slope", [Slope1] )
RETURN
    AVERAGEX ( -- Since the Y-values are percentages, it makes more sense to average than to sum
        DISTINCT ( 'data'[Date of test] ),
        Intercept + Slope * 'data'[Date of test]
    )

Putting this into a visual similar to what you've described:

Hope this helps! Please post back if needed.

Kind regards