Calculated column DAX for cumulative cost saving

[neees78]

Good day all, 

I've the following table - need daily cost to show cumulative savings after each new saving is added in 

Stating at daily cost 100K and then that column should reduce each time new saving is added in

Sorted by Target Date 

[tamerj1 · Accepted solution]

Hi @neees78 
Please refer to attached sample file with the proposed solution. Hope this is what you're looking for

Daily Cost = 
VAR StartingValue = 100000
VAR CurrentDate = SELECTEDVALUE ( 'Table'[Target Date] )
VAR CurrentID = SELECTEDVALUE ( 'Table'[ID] )
VAR SelectedTable = ALLSELECTED ( 'Table' )
VAR TableOnAndBefore = 
    FILTER ( 
        SelectedTable, 
        VALUE ( 'Table'[Target Date] ) * 1000000 + 'Table'[ID] 
            <= VALUE ( CurrentDate ) * 1000000 + CurrentID 
    )
VAR AccumulatedSaving = SUMX ( TableOnAndBefore, 'Table'[New Savinng] )
RETURN
    IF ( 
        HASONEVALUE ( 'Table'[ID] ),
        StartingValue - AccumulatedSaving
    )

[neees78]

@tamerj1    it worked perfectly -   thanks a lot 

[tamerj1]

@neees78 
Is new Saving a measure or a column? Are all columns coming from the same table?

[neees78]

Yes - all columns in single table , the "New Saving" is a column

[tamerj1]

@neees78 
Is new Saving a measure or a column?

[neees78]

the new saving is column - I just added it to explain - starts at 100K then it should reduce 

[FreemanZ]

hi @neees78 

do you have other columns in the dataset?

[neees78]

yes - Title (txt) & ID (Number)