How to do LINESTX for a fixed set of dates not dependent on the dates displayed in the visual

[masplin]

I've been looking at this for days and just cant work out what is wrong with my code.

The objected is to do a LINEST calc based on the 12 previous months and then compare the last month with what would have been forecast to see how we are tracking.

Fcst Instructions £ LTM 1M Lag New = 
VAR
MAXMONTH=EOMONTH(CALCULATE(MAX (DateTable[Date]),ALLSELECTED(DateTable)),-2)+1
VAR
Months=1
VAR
InitialInst=CALCULATE(MIN(DateTable[Date]),FILTER(ALL(DateTable),[Cases Instructed (£) per 21D]>0))
VAR
MINMONTH=MAX(EOMONTH(InitialInst,-1)+1,EOMONTH(MAXMONTH,-12)+1)
VAR
 data = ADDCOLUMNS(SUMMARIZE(FILTER(ALL(DateTable),DateTable[MonthYearDate]<=MAXMONTH && DateTable[MonthYearDate]>=MINMONTH),
                    DateTable[MonthYearDate]),
                    "Instructions", [Cases Instructed (£) per 21D]
                        )   
VAR coef =
    LINESTX (data, [Instructions], DateTable[MonthYearDate] )
VAR slope =
    SELECTCOLUMNS ( coef, [Slope1] )
VAR intercept =
    SELECTCOLUMNS ( coef, [Intercept] )
    
RETURN
    SELECTEDVALUE ( DateTable[MonthYearDate] ) * slope + intercept

This code works correctly if i have 1/3/23 to 1/3/24 selected in a table. The LINEST is working off 1/3/23 to 1/2/24 and fcst the 1/3/24 value. 

I've made a sample pbix Test 

Ideally I can deselect all the dates except the 1/3/24 as that the one I'm interested in.  However you can see if you deselect 1/3/23 in the filters The 1/2/24 jumps to 643k from 569k so a pretty big change in the slope.  However my calculations for MAXMONTH and MINMONTH are not changing so I thought I had locked the date range feeding into the LINESTX function. 

So the mission is that the data table feeding the LINESTX function doesnt change when you change the selected dates on the visual.  I just cant work out what is going on.  Any advice appreciated.

Mike

[v-kaiyue-msft]

Hi @masplin ,

The date range of your LINESTX function is not locked. Take maxmonth and minmonth out as measures separately and put them in the visual object. You can see that they will change as the filter changes.

Filter context operates at the model level. When you place a filter on a column, the filter affects the column, the table to which the column belongs, and all tables that are accessible through the relationship. For more details, please refer to the link: Row Context and Filter Context in DAX - SQLBI.

If your LINESTX function is based on a known fixed date calculation, then you can put the known date directly into the expression to ensure that it does not change. If not, you can also create a new table that has no relationship to the original table to get unfiltered dates for calculations.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[masplin]

Hi Clara.  I did put my minmonth and maxmonth in the visual and those dates dont change as i deselect the months in the filter panel so i'm a bit unclear what you did differently?  You can see below the inc or ex Mar23. The min nad max dates have not changed but the result has. that why i thought I locked the dates.

Also notice the count of rows in the ex March stillsay 12.  i worked out in Excel that the slope of 852.64 is the calcuation using all 12 dates, but the Mar23 input value has been set to zero. So i think my code is ocrrectly keeping 12 rows of dates, but for some reason only picking up 11 values of "Instructions".

I wanted this to be a rolling calcation so each month it calculates the forecast for the last month using the period 13 months previous to 1month previous. So they are not static dates as always relative to the current month.  Basically trying to use this as early warning system if cusotmer volume is deviating from the expected path based on last 12 months sales.   Ideally my visual just has the last month and displays the forecast value vs the actual so I can use it as a KPI. Having to keep all the input months in the visual makes it useable. 

Really appreciate if you can explain how to achieve this as to my poor brain what i have done should work.  In my data table I have taken ALL Datetable so shoudl ignore the filters then applied my min and max months, which I dont think are moving. 

Thanks

Mike

[masplin]

To prove the point I change my data table calcuation to have fixed dates and it still doenst work

 data = ADDCOLUMNS(SUMMARIZE(FILTER(ALL(DateTable),DateTable[MonthYearDate]<=DATEVALUE("1-Feb-2024") && DateTable[MonthYearDate]>=DATEVALUE("1-March-2023")),
                    DateTable[MonthYearDate]),
                    "Instructions", [Cases Instructed (£) per 21D]
                        )   

 So it isnt the date bit causing the issue. Its some wierdness affecting the "Instructions" b it of the table which must be some filtering going on inside the measure. The value in the LINEST is not a fixed number as in this example, but based on a sum of transaction values.  Its a chain of 5 measures to get to the one I need.  

[masplin]

Actually its isnt to do with the measures as doesnt work in the example which is just a table of numbers.  However there is a relationship between the datetable and the date on the Instruction table so is this limiting the value data reaching the calculation. 

[v-kaiyue-msft]

Hi @masplin ,

Filtering March 2023 to February 2024 vs. April 2023 to February 2024, maxmonth and minmonth will indeed not change. But at this time the third parameter of the LINESTX function will change with the selection of the filter. I think this is the reason why the final result will change.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[masplin]

Ok so I'm creating a static table in my data part, but witihn the LINEST fuction I'm then filtering it?  What is odd is the result is as if there are 12 rows of data, but first vlaue is set to zero. If the datetable was getting filtered to Apr/23 to Feb24 that would be 11 datapoints and would be a different result. 

In this excel file you can see the slope I'm getting is as on the right where the mar23 point is just zeroed out, but calcuated over all 12 rows i.e.including Mar23 row.  If it was just filtering Datetable you would have 11 rows in the calculation and slope would be 217 not 852.

Just can't get my head around this

LINEST 

[masplin]

You seem to be saying instead of using just DateTable[MonthYearDate] which is affected by the visual filters i need instead to tel lthe LINEST function to use a fixed set of datetable values Mar23-Feb24.  I tried using VALUES or CALCULATETABLE but cant get anything to work?

It must be possible to get LINEST to calcuate without being impacted by the visual filters?  Thanks

[masplin]

Tried this but oddly says some comflict between comaprsion of date and text

VAR coef =
    LINESTX (data, [Instructions], SELECTCOLUMNS(FILTER(ALL(DateTable),DateTable[MonthYearDate]<=MAXMONTH & DateTable[MonthYearDate]>=MINMONTH),DateTable[MonthYearDate]))