Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Running total based on other columns

Hi! How do I make a running total per week based on consumption? I have a dataset looks like this

DimDate

DateRunningTotalConsumption 1Consumption 2Restock
2020-01-01 -10-155
2020-01-02 -5-510
2020-01-03 -20-1015
2020-01-04 -5-1516
2020-01-05 -15-518
2020-01-06 -10-52
2020-01-07 -15-1018
2020-01-08 -20-1520
2020-01-09 -5-2025
2020-01-10 -10-530

 

Running total will start with a measure like this

CurrentInv = CALCULATE(SUM(Inventory[InvBalance]), DimDate[Date]), and it should change based on the information in DimDate for forecast. So assuming at the moment CurrentInv = 100, I am looking for 

DateRunningTotalConsumption 1Consumption 2Restock
2020-01-01100-10-155
2020-01-0280-5-510
2020-01-0380-20-1015
2020-01-0465-5-1516
2020-01-0561-15-518
2020-01-06 -10-52
2020-01-07 -15-1018
2020-01-08 -20-1520
2020-01-09 -5-2025
2020-01-10 -10-530

2020-01-01 = 100-10-15+5 = 80 --> value for RunningTotal for 2020-01-02

2020-01-02 = 80-5-5+10=80--> value for RunningTotal for 2020-01-03

2020-01-03 = 80-20-10+15=65--> value for RunningTotal for 2020-01-04 and so on.

 

How do I do that? Thanks!

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    is this what you want?

    Column = 100+SUMX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Consumption 1])+SUMX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Consumption 2])+SUMX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Restock])

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    is this what you want?

    Column = 100+SUMX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Consumption 1])+SUMX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Consumption 2])+SUMX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Restock])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi ryan_mayu It almost works! Just one more problem:

       

      My starting point is a measure from a different dataset like this-

      CurrentInv = CALCULATE(SUM(Inventory[InvBalance]), DimDate[Date])

       

      When I do 

      Column = CurrentInv+SUMX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Consumption 1])+SUMX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Consumption 2])+SUMX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])),'Table'[Restock])

      My data looks like this

      DateRunningTotalConsumption 1Consumption 2Restock
      2020-01-01100-10-155
      2020-01-020-5-510
      2020-01-03-15-20-1015
      2020-01-0411-5-1516
      2020-01-05 -15-518
      2020-01-06 -10-52
      2020-01-07 -15-1018
      2020-01-08 -20-1520
      2020-01-09 -5-2025
      2020-01-10 -10-530

       

      Any thoughts?

      • V-lianl-msft's avatar
        V-lianl-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Anonymous ,

         

        Try:

        RunningTotal = var cons1= CALCULATE(
SUM( 'Table'[Consumption 1] ),
FILTER(  ALL('Table') ,
SUMX( FILTER( 'Table', EARLIER( 'Table'[Date] ) <= 'Table'[Date] ), 'Table'[Consumption 1] )
)
)
var cons2 = CALCULATE(
SUM( 'Table'[Consumption 2] ),
FILTER(  ALL('Table') ,
SUMX( FILTER( 'Table', EARLIER( 'Table'[Date] ) <= 'Table'[Date] ), 'Table'[Consumption 2] )
)
)
var res = CALCULATE(
SUM( 'Table'[Restock] ),
FILTER(  ALL('Table') ,
SUMX( FILTER( 'Table', EARLIER( 'Table'[Date] ) <= 'Table'[Date] ), 'Table'[Restock] )
)
)
return [CurrentInv]+cons1+cons2+res

         

        DAX syntax is context based. We can't see your table relationship and detailed sample data. Maybe the DAX formula given can't be applied to your report perfectly. If the problem persists, please provide detailed information or create a suitable DAX for your report according to the given DAX.

         

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

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago
    You can use SUMX to get the totals per row. How does the raw data look like? Where is CurrentInv stored? Can you share the layout of your raw data please?