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

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

#### 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.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors