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 fda...
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

     

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