Running Total with Slicer

[viniciusfigueir]

I'd like to create a running total where I can filter the rows used by using a date slicer on another column. I have one data table with two columns, 'time' and 'dat', that looks like this:
table_a

time	dat
7	29/07/2019 13:33
15	02/01/2018 15:09
60	03/01/2018 15:53
25	30/07/2019 14:16
6	04/01/2018 15:11
8	31/07/2019 13:09
31	01/08/2019 13:25
14	02/08/2019 12:00
29	11/01/2018 11:00
20	05/08/2019 13:56
30	06/08/2019 13:59
20	07/08/2019 12:04
19	07/08/2019 12:05
137	08/08/2019 13:58
132	08/08/2019 14:00
20	09/08/2019 14:22
20	09/08/2019 14:24
13	22/01/2018 13:31
8	12/08/2019 15:43
7	12/08/2019 15:44
64	23/01/2018 11:50

To achieve a running total area chart I've created a new table, table_b:

table_b = GENERATESERIES(1;70)      (column name: 'value')
and its columns:

count = CALCULATE(COUNTROWS(table_a); FILTER(ALLSELECTED(table_a); table_a[time] <= 'table_b'[value]))

relative = 'table_b'[count] / MAX('table_b'[count])

However, as noted by the picture, a date slicer on column 'dat' does not affect my Area chart. How can I make the slicer affect the chart?

GENERATESERIES(1;70) should still be GENERATESERIES(1;70), but I'd like count to use only the subset of the rows selected by the date slicer.

[Greg_Deckler]

You will need to use a measure then, not a column. Columns can't be dynamic like that.

[viniciusfigueir]

How would I do that using a measure? thanks in advance

[v-lili6-msft · Accepted solution]

hi @viniciusfigueir 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, Try these two measure that based on your column formula

Count maeasure = CALCULATE(COUNTROWS(table_a), FILTER(ALLSELECTED(table_a), table_a[time] <= MAX(table_b[Value])))

relative measure = [Count maeasure]/CALCULATE(MAXX(table_b,[Count maeasure]),ALLSELECTED(table_b))

Regards,

Lin