How to recreate this in Power BI?

[michael_knight]

Hi,

I've got something which I need to do and I'm really struggling on how to implement the theory into Power BI. 

So the theory is, we have properties with multiple offers. Some fall through, some don't. If they fall through then the property can be resold.

In order to make sure the numbers aren't incorrectly inflated, we make sure to select an option showing if it's resold and then calculate the different from the original offer. The Original Offer is the one without a value in the "Resale?" column. 

I'm having a hard time implementing this in Power BI, does anyone have any ideas of how I can do this?

I'll include the Excel file just incase you want to see it

https://www.dropbox.com/scl/fi/ons5r9xr44yfej0b42ehb/Help-Resales.xlsx?dl=0&rlkey=u4guki5vfyf4jzjwei...

Cheers,

Mike

[michael_knight]

You're an absolute star, @v-yingjl Thank you!

[michael_knight]

Hi,

Just been having a play around and I'm still nonethewiser. Added another column to show which is the original offer. The Original Offer has returns "True". The the screenshot below the first offer should be the original so the bottom 3 should all be true, which isn't the case

On the screenshot below I was having a play around with the Total In figures and it turns out that the True or False depends on the Total In in some cases, which is unusual

Does anyone have any suggestions?

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

Cheers,

Mike

[v-yingjl · Accepted solution]

Hi @michael_knight ,

You can create the diff column like this:

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'[Resale?] ),
        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]
            )
    )

T/F column:

true or false = 
IF ( [diff] = 0, TRUE (), FALSE () )

Attached the sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@michael_knight , Try new column like

diff =
var _1 = maxx(filter(Table, [property name] = earlier([property name])) lastnonblankvalue([Date offer accepted], [Total IN]))
return
if (isblank(_1) , blank(), [Total IN] -_1)

[michael_knight]

Hi @amitchandak We're on the right track!

I've found an unusual issue with that measure. Some dates are doing it in reverse order. In the image below it should be using 1st January as the date to compare the rest to, rather than the 9th October

It seems to be working perfectly fine for the image below. I can't work out the difference

I'll add a PBIX file below

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

Cheers,

MIke