Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi I have similar problem like this
I need to create measure that calculate moving average for the last 6 months, but the date is not always sequencial
But the structure table is more complex, because it includes category and sub category. The current solution does not give the abiity to slice by those 2 columns
Month | Has Sales | Category | SubCategory | Value |
17-Jan | Yes | A | a1 | 10 |
17-Feb | Yes | A | a1 | 11 |
17-Mar | No | A | a1 | 12 |
17-Apr | No | A | a1 | 13 |
17-May | Yes | A | a1 | 14 |
17-Jun | Yes | A | a1 | 15 |
17-Jul | Yes | A | a1 | 16 |
17-Aug | No | A | a1 | 17 |
17-Sep | Yes | A | a1 | 18 |
17-Oct | Yes | A | a1 | 19 |
17-Nov | Yes | A | a1 | 20 |
17-Dec | Yes | A | a1 | 21 |
18-Jan | Yes | A | a1 | 22 |
18-Feb | No | A | a1 | 23 |
18-Mar | Yes | A | a1 | 24 |
18-Apr | No | A | a1 | 25 |
18-May | Yes | A | a1 | 26 |
18-Jun | Yes | A | a1 | 27 |
17-Jan | Yes | A | a2 | 10 |
17-Feb | Yes | A | a2 | 11 |
17-Mar | No | A | a2 | 12 |
17-Apr | No | A | a2 | 13 |
17-May | Yes | A | a2 | 14 |
17-Jun | Yes | A | a2 | 15 |
17-Jul | Yes | A | a2 | 16 |
17-Aug | No | A | a2 | 17 |
17-Sep | Yes | A | a2 | 18 |
17-Oct | Yes | A | a2 | 19 |
17-Nov | Yes | A | a2 | 20 |
17-Dec | Yes | A | a2 | 21 |
18-Jan | Yes | A | a2 | 22 |
18-Feb | No | A | a2 | 23 |
18-Mar | Yes | A | a2 | 24 |
18-Apr | No | A | a2 | 25 |
18-May | Yes | A | a2 | 26 |
18-Jun | Yes | A | a2 | 27 |
17-Jan | Yes | B | b1 | 10 |
17-Feb | Yes | B | b1 | 11 |
17-Mar | No | B | b1 | 12 |
17-Apr | No | B | b1 | 13 |
17-May | Yes | B | b1 | 14 |
17-Jun | Yes | B | b1 | 15 |
17-Jul | Yes | B | b1 | 16 |
17-Aug | No | B | b1 | 17 |
17-Sep | Yes | B | b1 | 18 |
17-Oct | Yes | B | b1 | 19 |
17-Nov | Yes | B | b1 | 20 |
17-Dec | Yes | B | b1 | 21 |
18-Jan | Yes | B | b1 | 22 |
18-Feb | No | B | b1 | 23 |
18-Mar | Yes | B | b1 | 24 |
18-Apr | No | B | b1 | 25 |
18-May | Yes | B | b1 | 26 |
18-Jun | Yes | B | b1 | 27 |
Any idea would be really appreaciated
Thanks!
Solved! Go to Solution.
Hi @andrehawari
Try this measure
Measure =
VAR mydate =
SELECTEDVALUE ( Sheet1[Month] )
VAR cate=SELECTEDVALUE(Sheet1[Category])
VAR subcate=SELECTEDVALUE(Sheet1[SubCategory])
VAR Previous6 =
TOPN (
6,
FILTER ( ALL ( Sheet1 ), Sheet1[Month] < mydate && Sheet1[Has Sales] = "Yes"&&[Category]=cate&&[SubCategory]=subcate ),
[Month], DESC
)
RETURN
AVERAGEX ( Previous6, [Value] )
Best Regards
Maggie
Hi,
What result are you expecting?
Hi @andrehawari
Try this measure
Measure =
VAR mydate =
SELECTEDVALUE ( Sheet1[Month] )
VAR cate=SELECTEDVALUE(Sheet1[Category])
VAR subcate=SELECTEDVALUE(Sheet1[SubCategory])
VAR Previous6 =
TOPN (
6,
FILTER ( ALL ( Sheet1 ), Sheet1[Month] < mydate && Sheet1[Has Sales] = "Yes"&&[Category]=cate&&[SubCategory]=subcate ),
[Month], DESC
)
RETURN
AVERAGEX ( Previous6, [Value] )
Best Regards
Maggie
Hi Maggies,
thanks a lot ! It works now, however, I was wondering, since my data will have have millions of row in azure analysis service database, will it affect performance badly using the averagex function?
thanks
Hi @andrehawari
Have you use the formula as a test?
Does it perform well?
If not, please tell me, I may change other formula which could perform better.
Best Regards
Maggie
The system perform well on the sample data, I will get back to you once the system is live in production because it will contains million of rows after that
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |