Forum Discussion

Vijaykumar_shan's avatar
Vijaykumar_shan
Frequent Visitor
2 years ago

Running total

Hello,

 

How to get running total to start where Expected is not blank

 

YearMonthActuals  Expected  Running total_actual
2023January7329305.26  
2023February7040769.37  
2023March8310650.13  
2023April8200281.83  
2023May8141290.64  7890089.22  8141290.64
2023June7756748.08  7525232.27  15898038.72
2023July7243086.24  7446243.08  23141124.96
2023August7935872.59  7900674.91  31076997.55
2023September7487803.65  7373772.55  38564801.20
2023October7571741.71  7771248.01  46136542.91
2023November7487853.55 
2023December7794475.08 
Need Help

4 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    Vijaykumar_shan 

     

    assuming you have a dimdate table : 

    create the below measure

    m = 

var res = 
var mind=  calculate(
min(dimdate[date]) , allselected(dimdate), not isblank(tbl_name[excpected] )) 


var maxd=  calculate(
max(dimdate[date]) ,  not isblank(tbl_name[excpected] )) 

var res = 
calculate (
sum(tbl_name[actual]) ,
dimdate[date] >=mind ,  dimdate[date<=maxd , removefilters(dimdate)
)

return res

     

     

     let me know if it works for you. 

     

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
    It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

    • Daniel29195's avatar
      Daniel29195
      Community Champion
      2 years ago

      Vijaykumar_shan 

      it seems that you dont have a dimdate . also i noticed that excepted is a measure not a column. 

       

      modify your code as follow : 

      m = 
        VAR mind =
            MINX (
                FILTER (
                    ADDCOLUMNS (
                        ALLSELECTED ( data[date] ),
                        "ex", [excpected]
                    ),
                    NOT ISBLANK ( [ex] )
                ),
                data[date]
            )
        VAR maxd = max(data[date])
            
        VAR res =
            CALCULATE (
                SUM ( tbl_name[actual] ),
                data[date] >= mind,
                data[date] <= maxd,
              
              REMOVEFILTERS ( data[date] )
            )
            
        RETURN res

       

       

       

      let me know if it works for you , 

       

       

       

      If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
      It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

      • Vijaykumar_shan's avatar
        Vijaykumar_shan
        Frequent Visitor
        2 years ago

        Formula is not working, I have actuals and expected both as measures. pls see below screenshot after I apply formula