Forum Discussion

michael_knight's avatar
michael_knight
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Previous rather than MINX

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

 

Need Help
Tips and Tricks
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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