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 saving is added in

Sorted by Target Date 

 

 

"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
    )

8 Replies

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

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

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

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