Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
hcmj
Frequent Visitor

Get the latest price not working

I have a price table like the below, which are connected to my calendar table through the field Activationdate in my fact table.
hcmj_0-1692894318588.png

I would like to have the priceamountacc to be the same for each day until a new price is entered for a certain date
09-03-2023   1,88

10-03-2023   1,88

11-03-2023   1,88

....
04-04-2023   1,81
05-04-2023   1,81
etc...

I have tried the following which is actually working on a SSAS Tabular Cube, but when trying to deploying it to a Power BI Dataset it does not work - any help is appreciated

LatestPrice:=
VAR MaxDate = MAX('Calendar'[Date])
VAR LatestClosingValue = CALCULATE(LASTNONBLANK(Fct_UnitPriceTransactions[PriceAmountAcc], 1),
ALLEXCEPT('Calendar','Calendar'[Date] ), 'Calendar'[Date] <= MaxDate)
RETURN LatestClosingValue

1 REPLY 1
johnt75
Super User
Super User

Try

Latest Price =
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR LastRow =
    INDEX (
        1,
        SUMMARIZE (
            Fct_UnitPriceTransactions,
            Fct_UnitPriceTransactions[PriceAmountAcc],
            Fct_UnitPriceTransactions[Activation date]
        ),
        ORDERBY ( Fct_UnitPriceTransactions[Activation date], DESC )
    )
VAR LastPrice =
    SELECTCOLUMNS ( LastRow, Fct_UnitPriceTransactions[PriceAmountAcc] )
RETURN
    LastPrice

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.