To get last 6 weeks from Fiscal week

[harshagraj]

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

[VijayP]

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

[harshagraj]

Hi @VijayP  thanks for the help but unfortunately we dont have date column. We have only FW & FY columns.

[VijayP]

You cna create a separate date columns based on the year and week available

[harshagraj]

Hi @VijayP Thankyou for the responce. Could you please help me to create that ? I have attached the Picture.

[Greg_Deckler]

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

[harshagraj]

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.

[Greg_Deckler]

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. 

[harshagraj]

@Greg_Deckler  It works good in Extract mode. But MAXX doesnot support in Direct mode. Any alternative for this is a biggest help!

[Greg_Deckler]

Actually, just use MAX('Table'[Column]) instead of MAXX, that should work.

[harshagraj]

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. 

[Greg_Deckler · Accepted solution]

I think,

Target Measure =
  VAR __FW = MAX([FW])
  VAR __Table = FILTER('Table',[Filter] = "Actuals" && [FW] = __FW - 1)
RETURN
  SUMX(__Table,[Amount])

[harshagraj]

@Greg_Deckler  I dont know how to thank you but thanks a lot. It worked perfectly.

[Greg_Deckler]

Happy to help! Glad it got you going!