DAX Measure to calculate monthly costs based on single date for each cost

[Anonymous]

Hi,

I have a table where each row represents a VM and an application with a planned decommission date and cost, in essence it looks something like this:

VM	Application	Decommission Date	Decommission Cost
VM01	Print application	01.01.2023	$1000
VM02	Print application	01.01.2023	$1000
VM03	File server	01.04.2023	$800
VM04	Email server	01.07.2023	$1500

What I would like to do:

• Create a DAX measure calculating the total monthly costs and see it on a timeline
• The decommission costs for each VM/application should be counted in all months until the month where it is decommissioned. This means the DAX in question should return $3300 until 01.2023, $2300 between 01.2023-04.2023 and $1500 between 04.2023 and 07.2023, $0 after 07.2023. 
• Then I would like to, if possible, use the date column to show the total decommission cost in a graph

What I tried:

TotalDecommissionCost = CALCULATE(SUM(Decommission Cost), ALL(Table)) - SUM(Decomission Cost)

This yielded wrong results.

Any suggestions welcome!

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, in my understanding, you want to calculate the sum of Decommission Cost when the date larger or equal to current date. In this way, 2023/1/1 will return 4300(1000+1000+800+1500), not 3300.

I this is the case, here's my solution.

Create a measure.

TotalDecommissionCost =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Decommission Date] >= MAX ( 'Table'[Decommission Date] )
    ),
    'Table'[Decommission Cost]
)

Get the result.

I attach the sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.