Forum Discussion
Calculate the amortized cost
Hi all,
I'm trying to create a dax measure to calculate the amortized cost.
The amortized cost shows the cost of the reservations spread over the total period of the reservation.
I tried to check microsoft documentation that explains how can I calculate the amortized cost and it shows an example How Azure calculates amortized costs
The below screenshot shows the combination of azure plan & reservations
And the alternative is current approach where I have the actual cost, if a reservation is order on may 1, all the cost will be on that day and not spread over the total period:
I've a charge start date column and a charge end date column and I've a measure called reserved instances cost:
Reserved Instances Cost = VAR countsrows = COUNTROWS ( 'Fact Partner Center Costs' ) VAR measure_cost = CALCULATE ( SUM ( 'Fact Partner Center Costs'[Total Amount] ), KEEPFILTERS ( 'Fact Partner Center Costs'[Category] = "Reserved Instances" ) ) return CALCULATE( measure_cost, FILTER ( 'Fact Partner Center Costs', countsrows > 0 ), KEEPFILTERS( 'Dim Azure Reserved Instances Contract'[Contract Client Id]<> BLANK () ) )
and after I tried to create this measure:
Reserved Instances Daily Amortized Cost =
SUMX (
'Fact Partner Center Costs',
VAR ContextDate = MAX ( 'Date'[Date] )
VAR CostStartDate = CALCULATE ( MIN ( 'Fact Partner Center Costs'[Charge Start] ), ALL ( 'Date' ) )
VAR CostEndDate = CALCULATE ( MAX ( 'Fact Partner Center Costs'[Charge End Date] ), ALL ( 'Date' ) )
VAR ReservedInstancesCost =
CALCULATE (
SUMX ( VALUES ( 'Date'[Day] ), [Reserved Instances Cost] ),
ALL ( 'Date' )
)
RETURN
IF (
ContextDate >= CostStartDate && ContextDate <= CostEndDate,
DIVIDE ( ReservedInstancesCost, COUNTROWS ( DATESBETWEEN ( 'Date'[Date], CostStartDate, CostEndDate ) ), 0 ),
BLANK ()
)
)
It showing the correct values but If I apply a filter to one of the slicers I have, called Azure Reserved Instances Contract Key, it doesn't break down the values by day, only for the 22nd
Thanks a lot !
1 Reply
- amitchandak
Super User
coding7 , check if this approach can help
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM