Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Get previous value from index slicer dynamically DAX

Hi everybody,

 

I am creating a report for which I use and index slicer. I want to compare a value from the current selection with the value previous to the current selection.

 

IndexProjectIDValueText
1

P1

100XYZ1
1P2200XYZ2
1P3250XYZ3
2P1150XYZ4
2P2250XYZ5
2P3300XYZ6
3P1200 XYZ7
3P2300 XYZ8
3P3350 XYZ9

 

 

So if i select index "3" in my slicer i'd like to see the following for P1 and P2:

ProjectPrev. Valuecurr. valuecurr text
P1150200XYZ7
P2250300XYZ8

 

And if I select index "2" i'd like to see:

ProjectPrev. Valuecurr. valuecurr text
P1100150XYZ4
P2200250XYZ5

I found a lot of relative date functions that look similar, but are not sufficient for this solution. Also i need a solution for numeric values and Text values. 

 

Is there anybody that can help me out? 

 

Thanks!

Slicer

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

     

    Prev value: = 
CALCULATE ( SUM ( Data[Value] ), Data[Index] = MAX ( Data[Index] ) - 1 )

     

    Current value: = 
SUM( Data[Value] )

     

    Current text: = 
SELECTEDVALUE( Data[Text] )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Now i have an additional question of which I hoped would have been solved by your solution, but it isn't.

     

    CALCULATE ( SUM ( Data[Value] ), Data[Index] = MAX ( Data[Index] ) - 1 )

     

    There is an additional filter that I want to include to this formula. I want to add another filter in this formula in which i select for a specific value in another table. 

     

    Previous score = 
CALCULATE( 
SUM( 'subject_score'[Score]),
 Index[index] = MAX(Index[index) - 1),
 FILTER('subject_score', 'subject_score'[subject] = "Money"))

     

    With this addition the value does not show in my table. Am i doing something wrong while using a double filter?

    new data example:

     

    Thanks already!