Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Trend Analysis - Subsequent Data Points

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!

2 REPLIES 2
amitchandak
Super User
Super User

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
Not applicable

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors