Calculate the amortized cost

[coding7]

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 !

[amitchandak]

@coding7 , check if this approach can help
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM