Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all I need to get a KPI visual it should display last 6 weeks for the amount and filter to be applied is Actuals.
And also target is the Previous week. Please help me to achieve this.
FW Amount Filter
1 | 17775 | Actuals |
1 | 15811 | Budget |
2 | 75976 | Actuals |
2 | 19237 | Budget |
3 | 11759 | Actuals |
3 | 16570 | Budget |
4 | 44845 | Actuals |
4 | 5427 | Budget |
5 | 74479 | Actuals |
5 | 18140 | Budget |
6 | 45633 | Actuals |
6 | 14782 | Budget |
7 | 44307 | Actuals |
7 | 15663 | Budget |
8 | 83742 | Actuals |
8 | 14049 | Budget |
9 | 38725 | Actuals |
9 | 15490 | Budget |
10 | 62828 | Actuals |
10 | 3911 | Budget |
11 | 36258 | Actuals |
11 | 15254 | Budget |
12 | 98433 | Actuals |
12 | 16341 | Budget |
13 | 81397 | Actuals |
13 | 10890 | Budget |
14 | 70454 | Actuals |
14 | 3595 | Budget |
15 | 70050 | Actuals |
15 | 3920 | Budget |
16 | 37456 | Actuals |
16 | 15300 | Budget |
17 | 40288 | Actuals |
17 | 12786 | Budget |
18 | 50304 | Actuals |
18 | 19443 | Budget |
19 | 10167 | Actuals |
19 | 4549 | Budget |
20 | 16785 | Actuals |
20 | 17660 | Budget |
21 | 86141 | Actuals |
21 | 4632 | Budget |
22 | 96800 | Actuals |
22 | 4899 | Budget |
23 | 10204 | Actuals |
23 | 18059 | Budget |
24 | 68222 | Actuals |
24 | 10216 | Budget |
25 | 20228 | Actuals |
25 | 11560 | Budget |
26 | 20926 | Actuals |
26 | 19250 | Budget |
27 | 82594 | Actuals |
27 | 11247 | Budget |
28 | 41238 | Actuals |
28 | 17816 | Budget |
29 | 47179 | Actuals |
29 | 17294 | Budget |
Solved! Go to Solution.
I think,
Target Measure =
VAR __FW = MAX([FW])
VAR __Table = FILTER('Table',[Filter] = "Actuals" && [FW] = __FW - 1)
RETURN
SUMX(__Table,[Amount])
In the first place , to get Time Intelligence you need to have a Date column else your goal cannot be achieved
Once you have a DAte and Date Dim Table , this is the DAX
Calculate(Sum(Amount),
Filter(All(DateDim),
DateDim[Date]>DateDim[Date]-42 && Max(DateDim[Date]))
42 is for 6 Weeks
Let me know if this helps
Regards
Vijay Perepa
watch my videos at www.youtube.com/perepavijay
Proud to be a Super User!
Hi @VijayP thanks for the help but unfortunately we dont have date column. We have only FW & FY columns.
You cna create a separate date columns based on the year and week available
Proud to be a Super User!
Hi @VijayP Thankyou for the responce. Could you please help me to create that ? I have attached the Picture.
Not sure I fully understand but perhaps:
Measure =
VAR __FW = MAX([FW])
VAR __MinFW = __FW - 6 + 1
VAR __Table = FILTER('Table',[Filter] = "Actuals" && [FW] <= __FW && [FW] >= __MinFW)
RETURN
SUMX(__Table,[Amount])
Hi @Greg_Deckler thanks for the quick solution. Please correct me if i am doing wrong. I have selected KPI chart and placed the Measure formula which you have helped and in Trend axis i have put FW. Still the KPI card's trend axis shows all the weeks from 1-29. I just have to show trend from week 24-29.
OK, you should just be able to filter your visual. If you want to get fancy about it, create a column like:
Column =
VAR __FW = MAXX('Table',[FW])
VAR __MinFW = __FW - 6 + 1
RETURN
IF([FW] > __MinFW,"Include","Exclude")
Then just filter your axis on that column. This way it stays up-to-date as data gets refreshed.
@Greg_Deckler It works good in Extract mode. But MAXX doesnot support in Direct mode. Any alternative for this is a biggest help!
Wow @Greg_Deckler perfect!! thanks a lot for this. It is working just the way i wanted. One last question I have to use last week' Amount in target goal. In this case Week 29 will be current week and in the Target i want the amount of week 28.
I think,
Target Measure =
VAR __FW = MAX([FW])
VAR __Table = FILTER('Table',[Filter] = "Actuals" && [FW] = __FW - 1)
RETURN
SUMX(__Table,[Amount])
@Greg_Deckler I dont know how to thank you but thanks a lot. It worked perfectly.
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |