Top display previous 4 weeks data

[harshagraj]

Hello all,

I am on direct query mode
I have two columns Fiscal Week and Amount and i have to display previous 4 weeks(25,26,27,28) in a table. Fiscal week column will have 1-52 weeks and amount will have data only till 28th week. I tried measure like
Var _max week = calculate(max(fiscal_week,Filter(Amount<>Blank)
Var_previousweek = _maxweek-3
Return
Calculate(Sum(Amount), Filter(fiscal_week >=_previousweek && fiscal_week<= _maxweek))) and this worked for a card and not for a table.

WeeksAmount

1	6773
2	7117
3	7112
4	7539
5	6837
6	6257
7	6398
8	6036
9	5698
10	6740
11	7097
12	7516
13	6480
14	5746
15	5355
16	7021
17	5975
18	7042
19	7344
20	7290
21	6615
22	7794
23	6363
24	6244
25	6990
26	7432
27	7029
28	5602
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

[amitchandak · Accepted solution]

@harshagraj , a small change, Try

Var _max week = maxX(filter(allselected(Table), [Amount]<>Blank), [fiscal_week])
Var_previousweek = _maxweek-3
Return
Calculate(Sum(Amount), Filter(Table, [fiscal_week] >=_previousweek && [fiscal_week] <= _maxweek))

[Jihwan_Kim]

Hi, @harshagraj 

Please try the below calcualated measure.

Last four weeks =
VAR maxweek =
CALCULATE (
MAX ( Data[Weeks] ),
FILTER ( ALL ( Data ), NOT ISBLANK ( Data[Amount] ) )
)
VAR lastfourweeksamount =
CALCULATE (
SUM ( Data[Amount] ),
KEEPFILTERS (
FILTER ( ALL ( Data ), Data[Weeks] >= maxweek - 3 && Data[Weeks] <= maxweek )
)
)
RETURN
lastfourweeksamount

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[amitchandak · Accepted solution]

@harshagraj , a small change, Try

Var _max week = maxX(filter(allselected(Table), [Amount]<>Blank), [fiscal_week])
Var_previousweek = _maxweek-3
Return
Calculate(Sum(Amount), Filter(Table, [fiscal_week] >=_previousweek && [fiscal_week] <= _maxweek))

[harshagraj]

Hi @amitchandak could you please help me to take difference of previous week also? I want to calculate change in % from previous week.

[harshagraj · Accepted solution]

wow worked like a charm. Thanks a lot.