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 und...
Need Help
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