cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wilm117
Helper I
Helper I

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!

2 REPLIES 2
Wilm117
Helper I
Helper I

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:

Wilm117_0-1666873406221.png

 

Thanks already!

 

 

 

Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_0-1666840728983.png

 

 

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

 

Current value: = 
SUM( Data[Value] )

 

Current text: = 
SELECTEDVALUE( Data[Text] )

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


Go to My LinkedIn Page


Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors