Forum Discussion

neees78's avatar
neees78
Icon for Helper II rankHelper II
3 years ago
Solved

Calculated column DAX for cumulative cost saving

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 s...
"Calculated Column"
Split Cost
  • tamerj1's avatar
    tamerj1
    3 years ago

    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
    )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
neees78
Icon for Helper II rankHelper II
3 years ago

tamerj1    it worked perfectly -   thanks a lot