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 th...
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

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