Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Martin Pogacnik]

Hi guys,

I cannot get this to work.  My table has a column DATE, a column PRICE, and a column GRADE.  There are two GRADE types (text).  On any single day, there can be a price for one or the other or both or none of the grades.  I need a measure calculating the difference between the most recent prices between both grades on any single day.  Any ideas?

Thanks,

Martin

——————————
Martin Pogacnik
——————————

[Nicolas MENDEZ]

Hi…

If everithing is clear in my mind..; here is my contrib…

Price diff =
VAR __LastPrice =
    CALCULATE (
        LASTNONBLANK (
            Prices[Price],
            Prices[Price]
        ),
        FILTER (
            ALL ( Prices ),
            Prices[Grade]
                <> SELECTEDVALUE ( Prices[Grade] )
        ),
        FILTER (
            ALL ( Prices ),
            Prices[Date]
                <= SELECTEDVALUE ( Prices[Date] )
        )
    )
VAR __Result =
    SELECTEDVALUE ( Prices[Price] ) - __LastPrice
RETURN
    __Result
?

I built a table called ‘Prices’ as following :

I hope this helps…

Feedback welcome

——————————
Nicolas MENDEZ
Data & BI consultant
Becom Consulting
——————————
——————————————-

[Martin Pogacnik]

Hi Nicolas,

thank you so much for taking the time!  I am a total novice here, so really appreciate it.  The result is as it should be, except my description was incomplete:  how would the formula change, if Price diff were always calculated only in one direction, i.e. Grade A – Grade B, such that, for instance, the result in the third row (22-Jan, 64, Grade B) would show 5 rather than -5 as Price Diff?

Merci,

Martin

——————————
Martin Pogacnik
——————————
——————————————-

[Ka Ly]

Martin, if you are just interested in the price difference then use the DAX function:

DAXCopy

ABS(<number>)

To get your value to always show in a positive number regardless of the result.  A-B or B-A
https://docs.microsoft.com/en-us/dax/abs-function-dax
In this instance  after the return, you type
ABS(_Result)

——————————
Ka Ly
Clinical Informatics Program Manager
——————————
——————————————-

[Martin Pogacnik]

Hi Kay Li,

thank you for this.  Actually, I was not looking for absolute difference, but one-directional difference only, and with an added complication that there are more than two grades.

The approach suggested by Nicolas, modified slightly with an additional IF statement, worked here as well.

Price diff =

VAR __LastPriceA =

CALCULATE(

    LASTNONBLANK(Prices[Price],Prices[Price]),

    FILTER(ALL(Prices), Prices[Grade] = “Grade A”),

    FILTER(ALL(Prices),Prices[Date] <= SELECTEDVALUE(Prices[Date]))

    )

    VAR __LastPriceB =

    CALCULATE(

    LASTNONBLANK(Prices[Price],Prices[Price]),

    FILTER(ALL(Prices), Prices[Grade] = “Grade B”),

    FILTER(ALL(Prices),Prices[Date] <= SELECTEDVALUE(Prices[Date]))

    )

    VAR __Result =

    if(

        SELECTEDVALUE(Prices[Grade]) = “Grade A”,

        SELECTEDVALUE(Prices[Price]) – __LastPriceB,

        if(

            SELECTEDVALUE(Prices[Grade]) = “Grade B”,

        __LastPriceA – SELECTEDVALUE(Prices[Price]),

       __LastPriceA – __LastPriceB )

    )

    RETURN __Result

You guys are great, I learn so much from your posts.  Thanks for helping me out on this!

Best,

Martin

——————————
Martin Pogacnik
——————————
——————————————-