CODEWITHSUNDEEP
powerbidax
Sam
Sam

Reputation: 61

DAX statement to find the latest non blank value in table visual with dates

This question is about power bi and dax.

I need help with a DAX statement to find the latest non blank value in table visual with dates.

I have four (4) tables that I need to show in a table visual.

First is a basic date table. Second is item (SKU) dimension table.

Third is a price history table, lets call this one purchase price. It has a row for every event when a SKUs purchase price changes.

Date SKU PurchasePrice
01/01/2023 1 0.52
02/02/2023 1 0.62
03/01/2023 2 1.70
03/04/2023 2 1.56

Fourth is another price table, let's call it consumer price table. It has a row for every event when a SKUs consumer price changes.

Date SKU ConsumerPrice
01/01/2023 1 2.50
02/01/2023 1 2.80
01/01/2023 2 5.50
02/01/2023 2 5.00

The result I am after would be something like this:

Date(from DATE table) SKU (from item) ConsumerPrice PurchasePrice
01/01/2023 1 2.50 0.52
01/02/2023 1 0.52
01/03/2023 1 0.52
01/04/2023 1 0.52
01/05/2023 1 0.52
... ... ... ...
02/01/2023 1 2.80 0.52
02/02/2023 1 0.62
02/03/2023 1 0.62

The intended function is for table visual to show the latest non blank value in purchase price column.

There are a few dax statements I have tried so far with little to no luck.

Some of them:

PurchasePrice = lastnonblankvalue(Table[Date], [PurPrice])

PurchasePrice = calculate([PurPrice], filter(Table,Table[Date] = calculate(max(Table[Date])))

PurchasePrice = 
CALCULATE(
    // get sum of value column
    [PurPrice], 
    // where value is not blank, and date is max date
    [PurPrice] <> BLANK() && Table1[Date] = MAX(Table1[Date])
)

Any help would be greatly appreciated!

Upvotes: 0

Views: 623

Answers (1)

Sam
Sam

Reputation: 61

I figured it out. In case anyone is wondering here is the solution:

PurchasePrice = 
VAR vLatestNonBlankDate =
    CALCULATE (
        MAX ( Date[Date] ),
        FILTER (
            ALL ( PurPrice),
            PurPrice[Date] <= MAX ( Date[Date] )
                && PurPrice[Price] <> BLANK ()
                && PurPrice[Item] = SELECTEDVALUE ( Item[Item] )
        )
    )

VAR vLatestNonBlankPrice =
    CALCULATE (
        AVERAGE(PurPrice[Price]),
        FILTER ( ALL ( Date ), Date[Date] = vLatestNonBlankDate )
    )

RETURN
    IF (
        SELECTEDVALUE ( Date[Date] ) < TODAY (),
        vLatestNonBlankPrice,
        BLANK ()
    )

The solution is modified dax from https://www.youtube.com/watch?v=_PyMDc42hgs&ab_channel=AllThingsPowerBIWithAdebola

Upvotes: 0

Related Questions