How to detect if text value was changed on previous month

[Dennis_S]

Hello,

I can not correctly formulate the question to find it.

So, I have the list of some accounts and their statistics arranged by year and month.

Every month the 'plan' of every account is written in the corresponding cell:

If I'm creating the Matrix in Power BI - I see the following picture (row_id 85 is the most informable):

I want to detect all changes in plans for accounts by months - from professional to platinum and from platinum to basic (all these combinations will be determined by me) - and show the 1 for the current month. These values will be used many times to calculate some margins etc. So, for row_id 85 I want "1" at third column exactly for ProToPlatinum column (measure?).

I'll appreciate for any help or the corresponding topic on the forum.

Thanks.

[Phil_Seamark · Accepted solution]

Hi @Dennis_S

I think this calculated column might be close.  I have attached a basic PBIX file showing it working.

Measure = 
VAR CurrentMeasure = MINX('Table1',[Plan])
VAR PreviousMeasure = 
    MINX(
        FILTER(
            ALL('Table1'),
            'Table1'[Account_ID] = MAX('Table1'[Account_ID]) && 
            EDATE(DATE('Table1'[Year],'Table1'[Month],1),1) = DATE(MAX('Table1'[Year]),MAX('Table1'[Month]),1)
            ),[Plan])
RETURN 
    IF(
        CurrentMeasure<>PreviousMeasure && NOT ISBLANK(PreviousMeasure),
        1,
        0)

[Dennis_S]

Phil, it works perfect, but this measure detects every change in plan.

But I want to detect exactly the change from one determined position to another determined too. These two positions will be determined by me - so for all combinations there will be three 'upgrade' measures -> BasicToProfessional, BasicToPlatinum and ProfessionalToPlatinum. And so on three 'downgrade' measures -> ProfessionalToBasic, PlatinumToBasic and PlatinumToProfessional.

I'll try to understand this formula but if you send the solution for one measure - I'll be glad.

Thanks.

[Dennis_S]

Thanks, I found that the condition in the end could be determined as I need.

So BasicToPlatinum will such this on the last row:

IF(
        CurrentMeasure = "Platinum" && PreviousMeasure = "Basic",
        1,
        0)