Forum Discussion

mark2073's avatar
mark2073
Frequent Visitor
1 year ago

Result from last 8 consecutive data points

Line chart and table for illustration

My example metric ( I have 16 in total to replicate ) where I'm trying to determine if the result of the last 8 consecutive data points are either under the trending regression line ( result = -8 ) or last 8 consecutive data points are above the regression line and therefore 8. If there is a +1, 0 or -1 in the last 8 that disrupts an output of -8 / +8, then a Measure wont trigger an up arrow (+8 ) or down arrow ( -8 )
I have a DateTable starting from 08/01/2023 ( 8th Jan 2023 ) to current date and is updated weekly so 96 Sundays so far
I have a Volume measure extracting from a Main Data table and a Regression measure, utilising the Volume measure and DateTable

 to determine the trend slope

I've created an additional measure to get a visual of Vol and Reg and Result

I've tried many different functions but can't seem to come up with a method to fetch the last 8 consecutive data points and sum to get either -8 in this case or +8
The Regression line needs to anchor back to 08/01/2023 to keep the slope as is but TOPN ( 8 ) seems to always adjust to last 8 LastDayOfWeek no matter what I do

I'd rather no have to go down the Power Query route and have to build all this in, and other remaining metrics that need 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi mark2073 ,
    You can try the following measures

    IsAboveRegression = IF([Vol] > [Reg], 1, -1)
    Last8Sum = 
VAR Last8Dates = 
    CALCULATETABLE(
        TOPN(8, DateTable, DateTable[LastDayOfWeek], DESC),
        ALL(DateTable)
    )
RETURN
    SUMX(
        FILTER(
            DateTable,
            DateTable[LastDayOfWeek]IN SELECTCOLUMNS(Last8Dates, "Date", DateTable[LastDayOfWeek])
        ),
        [IsAboveRegression]
    )
    TriggerArrow = 
IF(
    [Last8Sum] = 8, 
    "Up Arrow", 
    IF(
        [Last8Sum] = -8, 
        "Down Arrow", 
        "No Arrow"
    )
)

    Final output

    Best regards,
    Albert He

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

     

     

    • mark2073's avatar
      mark2073
      Frequent Visitor
      1 year ago

      Hi v-heq-msft, thanks for your reply. Unfortunately it's still not quite there and wondering if I need to change an element(s) within the model as I'm not getting the "expected" result. Something is skewing somewhere behind the scenes and I can't break it down enough to find out where. I recreated your 3 x Measures and used the results below ( Volumes can still increase over time as Users complete their ongoing information )

      Place the Measure in a Card and I get -2 and not -8. From what I originally created in DAX, I was getting the same unexpected result. Plugged a question into ChatGPT, gave me similar DAX but still incorrect result. Yours was similar DAX and still getting something unexpected. I may have to try a new method