cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

How to detect if text value was changed on previous month

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:

4.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

5.png

 

 

 

 

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: How to detect if text value was changed on previous month

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)

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: How to detect if text value was changed on previous month

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)

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Highlighted
New Member

Re: How to detect if text value was changed on previous month

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.

Highlighted
New Member

Re: How to detect if text value was changed on previous month

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)

 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors