Previous rather than MINX

[michael_knight]

Hi,

I recently posted a thread when I needed help converting an Excel theory to Power BI. I got some brilliant help on here, however I need to adjust it.

This is what I need to do (diff is the aim)

I need to see the difference between an offer and it's prerequisite offer, rather than the very first offer.

Originally any offer after the first will check to see what the difference is between the Total In's of the first offer 

diff = 
VAR tb =
    SUMMARIZE (
        ALL ( Theory ),
        'Theory'[Property Name],
        Theory[Date Offer Accepted],
        Theory[Resale?],
        'Theory'[Total IN],
        "T", CONVERT ( 'Theory'[Total IN], INTEGER )
    )
RETURN
    IF (
        ISBLANK ( Theory[Date Offer Accepted] ),
        0,
        'Theory'[Total IN]
            - SUMX (
                FILTER (
                    tb,
                    [Date Offer Accepted]
                        = MINX(  
                            FILTER ( tb , [Property Name] = EARLIER ( Theory[Property Name] ) ),
                            [Date Offer Accepted]
                        )
                        && [Property Name] = EARLIER( Theory[Property Name] )
                ),
                [T]
            )
    )

This is the foruma that was created, and I need to make some amendments to make sure it will show the difference between the previous date rather than the first date.

I've made a column which is the intended goal (diff - excel version) which will hopefully help in showing what the aim is 

Here's the PBIX file: 

https://www.dropbox.com/s/6r1v3a3rqztbfec/Help%20-%20Resale.pbix?dl=0

Cheers,

Mike

[michael_knight]

Thank you very much, that worked for me @mahoneypat 

Thank you too @Anonymous  for helping me also, much appreciated  

[Anonymous]

Hi @michael_knight ,

You could try to follow these steps:

1. Add a rank column:

rankColumn =
VAR a = [Property Name]
VAR t1 =
    FILTER ( ALL ( 'Theory' ), 'Theory'[Property Name] = a )
RETURN
    RANKX ( t1, RANKX ( t1, [Date Offer Accepted],, ASC, SKIP ),, ASC, SKIP )

2.Create a measure: 

=
VAR _pre =
    CALCULATE (
        MAX ( 'Theory'[Total IN] ),
        FILTER (
            ALLEXCEPT ( Theory, Theory[Property Name] ),
            'Theory'[rankColumn]
                = MAX ( 'Theory'[rankColumn] ) - 1
        )
    )
RETURN
    IF ( _pre = BLANK (), 0, MAX ( 'Theory'[Total IN] ) - _pre )

My final output looks like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[mahoneypat · Accepted solution]

Here is a column expression that does that.  Note that in your example pbix, I had to change the data type of the Total In column to number.

Diff From Prev =
VAR thissale = Theory[Total IN]
VAR thisdate = Theory[Date Offer Accepted]
VAR prevdate =
    CALCULATE (
        MAX ( Theory[Date Offer Accepted] ),
        ALLEXCEPT (
            Theory,
            Theory[Property Name]
        ),
        Theory[Date Offer Accepted] < thisdate
    )
VAR prevsale =
    CALCULATE (
        SUM ( Theory[Total IN] ),
        ALLEXCEPT (
            Theory,
            Theory[Property Name]
        ),
        Theory[Date Offer Accepted] = prevdate
    )
RETURN
    IF (
        ISBLANK ( prevsale ),
        BLANK (),
        thissale - prevsale
    )

Regards,

Pat