Forum Discussion

harshagraj's avatar
harshagraj
Post Partisan
5 years ago
Solved

Top display previous 4 weeks data

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

16773
27117
37112
47539
56837
66257
76398
86036
95698
106740
117097
127516
136480
145746
155355
167021
175975
187042
197344
207290
216615
227794
236363
246244
256990
267432
277029
285602
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
39 
40 
41 
42 
43 
44 
45 
46 
47 
48 
49 
50 
51 
52 
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      harshagraj
      Post Partisan
      5 years ago

      wow worked like a charm. Thanks a lot.

    • harshagraj's avatar
      harshagraj
      Post Partisan
      5 years ago

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

       

       

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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.