Filtering max week by max non blank amount and max year

[harshagraj]

Hi all,

Please refer to the below data. I need a help to Prepare a KPI chart where i have to display Current week and previous weeks amount.

For current year i am using this 

curentyear = IF(ISBLANK(SUM('Table'[Amount])),"",MAX('Table'[Year Id])).

I need to get current week in current year and it should exclude blank in amount column. Presently it has 1-52 weeks in week column.
Also i have to display previous 6 weeks trend.

Week	Amount	Year Id
1	2929	2018
1	4690	2019
1	1848	2020
2	2614	2018
2	2540	2019
2	1035	2020
3	4248	2018
3	3235	2019
3	3941	2020
4	3607	2018
4	4587	2019
4	1944	2020
5	4439	2018
5	1211	2019
5	3204	2020
6	1782	2018
6	3299	2019
6	1797	2020
7	2805	2018
7	3173	2019
7	2782	2020
8	2138	2018
8	1544	2019
8	3926	2020
9	2385	2018
9	3185	2019
9	4019	2020
10	3574	2018
10	2053	2019
10	4434	2020
11	4160	2018
11	3282	2019
11	3674	2020
12	2514	2018
12	3828	2019
12	1854	2020
13	4507	2018
13	4734	2019
13	4878	2020
14	4978	2018
14	4283	2019
14	1298	2020
15	4671	2018
15	4235	2019
15	3310	2020
16	3753	2018
16	1351	2019
16	4851	2020
17	2593	2018
17	4312	2019
17	3082	2020
18	4184	2018
18	4144	2019
18	3023	2020
19	2594	2018
19	3050	2019
19	1099	2020
20	1826	2018
20	1675	2019
20	1824	2020
21	4056	2018
21	4670	2019
21	4198	2020
22	2025	2018
22	2979	2019
22	1106	2020
23	3023	2018
23	2772	2019
23	4048	2020
24	1967	2018
24	4737	2019
24	1068	2020
25	4797	2018
25	1002	2019
25	1516	2020
26	3340	2018
26	3590	2019
26	3098	2020
27	1888	2018
27	4417	2019
27	3530	2020
28	1853	2018
28	4866	2019
29	1052	2018
29	1468	2019
30	1854	2018
30	3526	2019
31	2927	2018
31	3346	2019
32	1158	2018
32	2842	2019
33	4911	2018
33	2370	2019
34	1502	2018
34	2375	2019
35	4508	2018
35	1683	2019
36	3261	2018
36	1875	2019
37	2465	2018
37	3525	2019
38	3142	2018
38	4345	2019
39	3682	2018
39	1854	2019
40	3272	2018
40	3311	2019
41	2419	2018
41	4050	2019
42	2800	2018
42	1039	2019
43	3412	2018
43	2100	2019
44	3485	2018
44	2417	2019
45	4740	2018
45	1820	2019
46	1630	2018
46	2588	2019
47	2578	2018
47	1400	2019
48	4195	2018
48	2872	2019
49	1433	2018
49	4850	2019
50	4507	2018
50	2051	2019
51	4715	2018
51	1518	2019
52	1910	2018
52	2710	2019

[Greg_Deckler]

So perhaps:

Current Week in Current Year Measure:
  VAR __CurrentYear = MAX('Table'[Year Id])
  VAR __CurrentWeek = MAXX(FILTER('Table',[Year Id]=__CurrentYear),[Week])
RETURN
  __CurrentWeek

[harshagraj]

Hi @Greg_Deckler  thanks a lot for the reply. Its actually displaying 52 for 2020. Actually it should display 27. In 2020 there is no amount after 27.

[Greg_Deckler]

Current Week in Current Year Measure:
  VAR __CurrentYear = MAX('Table'[Year Id])
  VAR __CurrentWeek = MAXX(FILTER('Table',[Year Id]=__CurrentYear) && NOT(ISBLANK([Amount])),[Week])
RETURN
  __CurrentWeek

[harshagraj]

Hi thanks for the reply do you mean NOT(ISBLANK(SUM(Amount))). If i use SUM then i am getting The expressions refers to multiple columns and cannot be converted to a scalar value.

[V-pazhen-msft]

@harshagraj 
That dax is used to create a calculate column, for a measure:

Measure = CALCULATE(MAX('Table'[Week]),FILTER(ALL('Table'),MAXX(FILTER('Table',EARLIER('Table'[ Year Id])='Table'[ Year Id]),1)),'Table'[Amount]<>BLANK())

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[harshagraj]

Hi @V-pazhen-msft  thanks for the reply. It is still showing 52 instead of 29(Max Week).