Trend Analysis - Subsequent Data Points

[Anonymous]

Hello everyone,

I need to do trend analysis, such as:
If the previous 7 data points are always decreasing then we flag 1 else we flag 0.
I have the following code which works, but I am thinking if there is a better than then having this somewhat heavy IF condition:

measure1 = 
var _7 = DATEADD(MasterCalendar[Date],-7,MONTH)
var _6 = DATEADD(MasterCalendar[Date],-6,MONTH)
var _5 = DATEADD(MasterCalendar[Date],-5,MONTH)
var _4 = DATEADD(MasterCalendar[Date],-4,MONTH)
var _3 = DATEADD(MasterCalendar[Date],-3,MONTH)
var _2 = DATEADD(MasterCalendar[Date],-2,MONTH)
var _1 = DATEADD(MasterCalendar[Date],-1,MONTH)

var L7MSales = CALCULATE([Local Sales],_7)
var L6MSales = CALCULATE([Local Sales],_6)
var L5MSales = CALCULATE([Local Sales],_5)
var L4MSales = CALCULATE([Local Sales],_4)
var L3MSales = CALCULATE([Local Sales],_3)
var L2MSales = CALCULATE([Local Sales],_2)
var L1MSales = CALCULATE([Local Sales],_1)

return

SWITCH(TRUE(),(L7MSales < L6MSales &&
            L6MSales < L5MSales &&
            L5MSales < L4MSales &&
            L4MSales < L3MSales &&
            L3MSales < L2MSales &&
            L2MSales < L1MSales),1,0)

Another one especially heavier is to analyze if three out of the past four data are higher then their treshold value.

var _6 = DATEADD(MasterCalendar[Date],-6,MONTH)
var _5 = DATEADD(MasterCalendar[Date],-5,MONTH)
var _4 = DATEADD(MasterCalendar[Date],-4,MONTH)
var _3 = DATEADD(MasterCalendar[Date],-3,MONTH)
var _2 = DATEADD(MasterCalendar[Date],-2,MONTH)
var _1 = DATEADD(MasterCalendar[Date],-1,MONTH)


var L6MSales = CALCULATE([Local Sales],_6)
var L5MSales = CALCULATE([Local Sales],_5)
var L4MSales = CALCULATE([Local Sales],_4)
var L3MSales = CALCULATE([Local Sales],_3)
var L2MSales = CALCULATE([Local Sales],_2)
var L1MSales = CALCULATE([Local Sales],_1)

var T6MSales = CALCULATE([Threshold],_6)
var T5MSales = CALCULATE([Threshold],_5)
var T4MSales = CALCULATE([Threshold],_4)
var T3MSales = CALCULATE([Threshold],_3)
var T2MSales = CALCULATE([Threshold],_2)
var T1MSales = CALCULATE([Threshold],_1)

return

    SWITCH(TRUE(),(L1MSales > T1MSales && L2MSales > T2MSales&& L3MSales > T3MSales),1,
    (L2MSales > T2MSales && L3MSales > T3MSales&& L4MSales >T4MSales),1,
      (L3MSales > T3MSales && L4MSales > T4MSales&& L1MSales >T1MSales),1,
        (L4MSales > T4MSales && L2MSales > T2MSales&& L1MSales >T1MSales),1,0)
                             

These are working measures, but I feel I am being highly inefficient. Any one with better approaches?

Thanks in advance!

[amitchandak]

Can you try doing count distinct of Month year for the last 7 Months in one go? And if it 7 then you have all seven months data

[Anonymous]

But I am not trying to know if I have data points on those months, I am trying to understand if they are growing one after the other or not.. (at least in the 1st example).

Thus I have all those conditions with a sequence coming from the latest to the news always comparing if they are bigger than previous