create measure with range to compare after performing a segmentation

[Syndicate_Admin]

Hi

I need create measure with range 2 to compare targeting selection or some other solution that allows me to compare the current evaluation with the previous one, given a previous segment of year.

Tables

TB_EVALUATION_HISTORY
ID_checkGroup	ID_pHistory	ID_parent	ID_current_Assessment	Period	RANK	EVALUATIONS
check_1	1	1	70	2022	1	3_LOW
check_3	2	1	35	2019	2	2_MEDIUM
check_1	3	2	63	2022	1	1_HIGH
check_2	4	2	37	2021	2	3_LOW
check_1.9	5	2	20	2020	3	2_MEDIUM
check_1.9	6	3	40	2020	3	2_MEDIUM
check_1.9	7	5	43	2020	1	1_HIGH
check_2	8	3	45	2021	2	1_HIGH
check_3	9	3	41	2019	3	3_LOW
check_1	10	3	49	2022	1	1_HIGH

Column detail
ID_checkGroup	Planning identification
ID_pHistory	sequential
ID_parent	id to review context
ID_current_Assessment	EValuation
Period	Plan period
RANK	column calculation to identify sequencial evaluation, considering all records

TB_EVALUATIONS
ID_current_Assessment	EVALUATIONS
70	3_LOW
35	2_MEDIUM
63	1_HIGH
37	3_LOW
20	2_MEDIUM
40	2_MEDIUM
43	1_HIGH
45	2_MEDIUM
41	3_LOW
49	1_HIGH

when i click on the period (2022).

The graph shows me all the records that were evaluated in 2022. But I can't show the previous evaluation done on the same record.

Expected graph, click on 2022

Expected graph, when i click on the period (2021).

THANKS!!

[PedroPablo]

I have sample file and it contains

Measure

• A_currentEvaluation: obtain minimun rank of period selected

Table

• TBHISTORUY: historical data with evaluation
• TBPeriod: Years to selected

To select the most recent evaluation I did this and it works fine.

A_currentEvaluation = 

VAR __Rank1= CALCULATETABLE(VALUES (TB_EVALUATION_HISTORY[RANK]), 
                FILTER(
                ALLSELECTED ( TB_EVALUATION_HISTORY ),
                [ID_parent] = MAX ( 'TB_EVALUATION_HISTORY'[ID_parent] )
                    && [Period] = SELECTEDVALUE ( 'TBPeriod'[Period] )
                    && TB_EVALUATION_HISTORY[RANK]= MIN(TB_EVALUATION_HISTORY[RANK])
                    )
                )

VAR __COUNT= COUNTROWS(__Rank1)

RETURN
__COUNT

Now I need help select the previous evaluation (yellow). 

TB_EVALUATION_HISTORY

ID_checkGroup	ID_pHistory	ID_parent	ID_current_Assessment	Period	EVALUATIONS	RANK
check_1	1	1	70	2022	3_LOW	1
check_3	2	1	35	2019	2_MEDIUM	2
check_1	3	2	63	2022	1_HIGH	1
check_2	4	2	37	2021	3_LOW	2
check_1.9	5	2	20	2020	2_MEDIUM	3
check_1.9	6	3	41	2020	2_MEDIUM	3
check_1.9	7	5	43	2020	1_HIGH	1
check_2	8	3	45	2021	1_HIGH	2
check_3	9	3	40	2019	3_LOW	4
check_1	10	3	49	2022	1_HIGH	1

TBPeriod

Period

2019

2020

2021

2022

código de rank (columna calculada) TB_EVALUATION_HISTORY

RANK = RANKX(FILTER(TB_EVALUATION_HISTORY,TB_EVALUATION_HISTORY[ID_parent]=EARLIER(TB_EVALUATION_HISTORY[ID_parent]))
        , TB_EVALUATION_HISTORY[ID_current_Assessment]
        ,
        ,DESC
        ,Dense)

pd: This is a reformulated questio from a previous one.

[Syndicate_Admin]

Hi man!!

Sorry for the response time.

It's not really the year+1. It is the previous evaluation, that is rank+1..

[v-stephen-msft]

Hi @PedroPablo ,

You want to filter out a year based on the slicer and then return the values of the filtered year and the previous year of the filtered year, right?

You may need a seperate year slicer. You could create a year table using DAX.

Table = DISTINCT('TB_EVALUATION_HISTORY'[Period])

Then create a measure as a visual level filter.

Measure =
VAR _year =
    MAX ( 'TB_EVALUATION_HISTORY'[Period] )
VAR _select =
    CALCULATE (
        MAX ( 'TB_EVALUATION_HISTORY'[Period] ),
        FILTER (
            ALLSELECTED ( TB_EVALUATION_HISTORY ),
            [ID_parent] = MAX ( 'TB_EVALUATION_HISTORY'[ID_parent] )
                && [Period] = SELECTEDVALUE ( 'Table'[Period] )
        )
    )
RETURN
    IF ( _select = _year || _select - 1 = _year, 1 )

Put the measure into the visual level filter of the table visual. Set up show items when the value is 1.

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.

[PedroPablo]

Sorry for the response time.

It's not really the year+1. It is the previous evaluation, that is rank+1..

I have created a new query with a better explication

https://community.powerbi.com/t5/Desktop/Identify-the-previous-evaluation-to-the-current-one/m-p/293...

[v-stephen-msft · Accepted solution]

Hi @PedroPablo ,

Since the two posts are the same issue, I helped you merge into one post.

Hope you don't mind.

Here's the soultion to return the previous evaluation(rank+1).

You can create the following measure. Still put it into the visual-level filter and set up show items when the value is 1.

Measure =
VAR _rank =
    CALCULATE (
        SUM ( 'TB_EVALUATION_HISTORY'[Rank] ),
        FILTER (
            ALLSELECTED ( 'TB_EVALUATION_HISTORY' ),
            [ID_parent] = MAX ( 'TB_EVALUATION_HISTORY'[ID_parent] )
                && [Period] = SELECTEDVALUE ( 'TBPeriod'[Period] )
        )
    )
RETURN
    IF (
        IF ( ISBLANK ( _rank ), BLANK (), _rank + 1 )
            = MAX ( 'TB_EVALUATION_HISTORY'[Rank] ),
        1
    )
v

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.

[PedroPablo]

@v-stephen-msft 

This effectly resolve the ticket, thanks!!

if it is possible, I need count register whit condition
 

[v-stephen-msft]

Hi @PedroPablo ,

This is a brand new requirement, for this, it is recommended that you reopen a new post, thank you.😁

Glad to hear that my solution works.

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.