Can use conditional comparing periods in a matrix table?

[andrelopes2711]

Hello, I have a measure that calculates the productivity of employees and I see this information per week in a matrix table,
I need to create a conditional that checks the productivity of the last two weeks and returns a text, for exemple,
if productivity is below last two weeks = "low productivity", if two weeks ago it was below the target and the last week was above
the target "in recovery",
if two weeks ago it was above the target and the last week it was below the target "attention".
we can do this in power bi using matrix table, like exemple below?

Thanks for help.

https://1drv.ms/u/c/c959b830a59f4f8b/EWtQjzd7r2FFsa8SVru8zwMBEk25QAvgRE2iLnMBdshW5A?e=SUuACu 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I tried to create a matrix visualization, and in my sample, the total column in the matrix visualization is considered as recent status column.

I do not know your target, and in my sample, I set the taget = 2.0

INDEX function (DAX) - DAX | Microsoft Learn

Productivity and status: = 
VAR _target = 2.0
VAR _productivity =
    SUM ( data[productivity] )
VAR _lastperiod =
    INDEX ( 1, ALL ( period[period] ), ORDERBY ( period[period], DESC ) )
VAR _secondlastperiod =
    INDEX ( 2, ALL ( period[period] ), ORDERBY ( period[period], DESC ) )
RETURN
    IF (
        HASONEVALUE ( period[period] ),
        _productivity,
        SWITCH (
            TRUE (),
            CALCULATE ( SUM ( data[productivity] ), _secondlastperiod ) < _target
                && CALCULATE ( SUM ( data[productivity] ), _lastperiod ) < _target, "Low Productivity",
            CALCULATE ( SUM ( data[productivity] ), _secondlastperiod ) < _target
                && CALCULATE ( SUM ( data[productivity] ), _lastperiod ) >= _target, "In recovery",
            CALCULATE ( SUM ( data[productivity] ), _secondlastperiod ) >= _target
                && CALCULATE ( SUM ( data[productivity] ), _lastperiod ) < _target, "Attention"
        )
    )

Productivity and status color condition: =
VAR _target = 2.0
VAR _productivity =
    SUM ( data[productivity] )
VAR _lastperiod =
    INDEX ( 1, ALL ( period[period] ), ORDERBY ( period[period], DESC ) )
VAR _secondlastperiod =
    INDEX ( 2, ALL ( period[period] ), ORDERBY ( period[period], DESC ) )
RETURN
    SWITCH (
        TRUE (),
        HASONEVALUE ( period[period] )
            && (
                MAX ( period[period] )
                    IN _secondlastperiod
                        || MAX ( period[period] ) IN _lastperiod
            ),
            SWITCH (
                TRUE (),
                _productivity < _target, "red",
                _productivity >= _target, "light green"
            ),
        HASONEVALUE ( period[period] ), "white",
        SWITCH (
            TRUE (),
            CALCULATE ( SUM ( data[productivity] ), _secondlastperiod ) < _target
                && CALCULATE ( SUM ( data[productivity] ), _lastperiod ) < _target, "red",
            CALCULATE ( SUM ( data[productivity] ), _secondlastperiod ) < _target
                && CALCULATE ( SUM ( data[productivity] ), _lastperiod ) >= _target, "light green",
            CALCULATE ( SUM ( data[productivity] ), _secondlastperiod ) >= _target
                && CALCULATE ( SUM ( data[productivity] ), _lastperiod ) < _target, "yellow"
        )
    )

[andrelopes2711]

Hello Jihwan_Kim, I'm new to the area and I didn't know I could change the Total column, I used your exemplo and some prolems occurred, for exemple, it shows every week even when it doesn't have produtivity and for some reason it doesn't return the other Staus, I attached my simple pbix

[Jihwan_Kim · Accepted solution]

Hi,

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

[andrelopes2711]

Jiwan_Kim, your help was great, but a new doubt, we were able to know how many agents are in "low productivity" or "Attention" using measure, to count each status and per agent we would need to create a summarized table per agent, week and make this comparison the last two weeks?

[foodd]

Hello @andrelopes2711 , remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.