Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need Help: Running Totals Do Not Filter

I need some assistance. My Daily Total column doesn't account for selected values in my slicer. When a item is selected the totals do not recalculate.

 

Can someone assist?

Daily Total = 
var fdate = MIN([activity_dt])
var ldate = MAX([activity_dt])
var rowdate= [activity_dt]
var finalrange = IF(DATEDIFF(fdate, rowdate, DAY) < 7, DATEDIFF(fdate, rowdate, DAY), 7)
var FilterTable = FILTER(Sheet1, [activity_dt] = rowdate)// >= rowdate-finalrange && [activity_dt] <= rowdate)
return
    CALCULATE(SUM([Gross_adds]), Filtertable)

The output here is as followed:

Daily Total: Is a running total of all categories and does not update regardless of what you select in the slicer.

Correct Daily Total: Is what I would like to see when I select 4 of those categories

 

 

 

 

DAX
Filters
Running Totals
  • Anonymous's avatar
    Anonymous
    4 years ago

    No worries. I figured it out yesterday. The updates should have been made in my 7 Day Running column (not shared) instead of the Daily Totals. In that code, the rowdate and finalrange is applied to where its less confusing. 

     

    7 Day running = 
var fdate = MIN([activity_dt])
var ldate = MAX([activity_dt])
var rowdate= [activity_dt]
var service = [service]
var finalrange = IF(DATEDIFF(fdate, rowdate, DAY) < 7, DATEDIFF(fdate, rowdate, DAY), 7)
var FilterTable = FILTER(Sheet1, [activity_dt] >= rowdate-finalrange && [activity_dt] <= rowdate && [service] = service)
var datatotals =   CALCULATE(SUM([Gross_adds]), Filtertable)
var results = if(finalrange <=0 ,  datatotals, datatotals/( finalrange+1))
return
  results

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    Looking at your DAX, I have a few questions .

    (1)I don't quite understand the meaning of rowdate, when you select multiple, this value will show null value. So, what are you getting this value for ?

    (2)If the value of finalrange is greater than or equal to 7 , how did you count the sum of [Gross_adds] ?

    From the correct daily totals you provided , it seems to be the sequential addition of the values you selected . So I am confused and hope you can give a detailed explanation .

     

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      No worries. I figured it out yesterday. The updates should have been made in my 7 Day Running column (not shared) instead of the Daily Totals. In that code, the rowdate and finalrange is applied to where its less confusing. 

       

      7 Day running = 
var fdate = MIN([activity_dt])
var ldate = MAX([activity_dt])
var rowdate= [activity_dt]
var service = [service]
var finalrange = IF(DATEDIFF(fdate, rowdate, DAY) < 7, DATEDIFF(fdate, rowdate, DAY), 7)
var FilterTable = FILTER(Sheet1, [activity_dt] >= rowdate-finalrange && [activity_dt] <= rowdate && [service] = service)
var datatotals =   CALCULATE(SUM([Gross_adds]), Filtertable)
var results = if(finalrange <=0 ,  datatotals, datatotals/( finalrange+1))
return
  results

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi , Anonymous 

    I am so glad that you have solved your issue . Please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.