Optimal DAX Measure that repeats the latest, smallest value on a different granularity

DAX
1

So let’s assume for the sake of simplicity the following table:

image
image430×164 4.87 KB

Two PurchaseOrder Numbers, with granularity on Transactions (not a sequence) representing when the Open Quantity (OpenQty) changes.

The idea is to now add another column which takes the latest (by Posting Date), smallest OpenQty for the PurchaseOrder and repeats them for all Transactions.

Like so:

image
image518×157 5.12 KB

My measure sorts the table by Posting Date and OpenQty and takes the top value. I ignores the other slicers with an ALLEXCEPT.

LatestOpenQty = 
CALCULATE(
    MINX(
        TOPN(1,FILTER(PurchaseOrder,PurchaseOrder[OpenQty]>=0),
                PurchaseOrder[PostingDate],DESC,
                PurchaseOrder[OpenQty],ASC),
            MIN(PurchaseOrder[OpenQty])),
    ALLEXCEPT(PurchaseOrder,PurchaseOrder[PurchaseOrder]),
    PurchaseOrder[OpenQty]>=0)

Although I have this working, it is part of a very large dataset and I feel like I am not using the most optimal approach.

Any advice?

Sample File:

LatestOpenQty.pbix (23.9 KB)

2

Hi @OniScion - You have created new field as Column that is not recommended. You can try creating a measure like below and add in your visual. It is providing same result.

MinQuantity = var a = CALCULATE(max(PurchaseOrder[PostingDate]),filter(all(PurchaseOrder),PurchaseOrder[PurchaseOrder] = max(PurchaseOrder[PurchaseOrder])))

RETURN
CALCULATE(min(PurchaseOrder[OpenQty]),filter(all(PurchaseOrder),PurchaseOrder[PurchaseOrder] = max(PurchaseOrder[PurchaseOrder]) && PurchaseOrder[PostingDate] = a))

Thanks
Ankit J

3

Hi @OniScion - Do above solution worked or any further help is needed ?

Thanks
Ankit J