Forum Discussion

kilala's avatar
kilala
Icon for Resolver I rankResolver I
2 years ago

How to get value for previous key

Hi all, I have 2 tables, fact and dimcycle. Both are connected via uniqueid (formatstring).

 

In dimcycle, there is cyclekey. When user select something from filter pane, this cyclekey will only show 1 result. E.g, now the cyclekey = 3. It will display current amount for cycle 3. At the same time, i want to show for previous cycle as well(cycle 2). 

 

I used this formula but nothing came up. 

Previous Cycle Amount =
VAR CurrentCycleKey = MAX(dim_cycle[CycleKey])
VAR PreviousCycleKey = CurrentCycleKey - 1
RETURN
CALCULATE([Current Cycle Amount],FILTER(dim_cycle,dim_cycle[CycleKey] = PreviousCycleKey)
)
 
What could be the problem?I tried to change to change the filter to this formula.. And I can see it display current cycle amount correctly. So, I'm not sure why it cannot show value for previous cycle (cycle 2)
 
Previous Cycle Amount =
VAR CurrentCycleKey = MAX(dim_cycle[CycleKey])
VAR PreviousCycleKey = CurrentCycleKey - 1
RETURN
CALCULATE([Current Cycle Amount],FILTER(dim_cycle,dim_cycle[CycleKey] = CurrentCycleKey)
)
 
Please help 😞

 

Need Help

3 Replies

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    2 years ago

    Hey kilala ,
    what is your target output?
    Can you provide a screen with target result?

    Regards

    • kilala's avatar
      kilala
      Icon for Resolver I rankResolver I
      2 years ago

      Hi, i want to display in table like this. it will display the current amount (cycle key depends on user selection) and previous amount (cyclekey - 1). 

       

      Currently it is able to show current cyclekey correctly but not previous cyclekey

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi kilala ,

     

    You should add ALL or ALLSELECTED function into your foumula.

    Previous Cycle Amount =
VAR CurrentCycleKey = MAX(dim_cycle[CycleKey])
VAR PreviousCycleKey = CurrentCycleKey - 1
RETURN
CALCULATE([Current Cycle Amount],FILTER(ALL(dim_cycle),dim_cycle[CycleKey] = PreviousCycleKey)
)

    For example:

     

    Best Regards,

    Stephen Tao

     

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