The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a price table like the below, which are connected to my calendar table through the field Activationdate in my fact table.
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
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
User | Count |
---|---|
26 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |