Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
75 | |
63 | |
52 | |
47 |
User | Count |
---|---|
215 | |
85 | |
61 | |
61 | |
60 |