cancel
Showing results 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

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 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 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?

2 REPLIES 2
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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.