Forum Discussion
Add new value to previous row
Hi guys
I have a very simple task but I could not get my head around achieveing this in DAX. I have a table with 5mins timestamps. There is a count for people going in (visitor_count_fw) and going out (vistor_count_bw). I calculated Total Change which is = visitor_count_fw+visitor_count_bw.
Now I need to calculate the fill state (calculated column), thus the previous timestamp fill state + Total Change. But how can I achieve this with EARLIER()? Also, the count would need to reset for every new day...
| datetime | visitor_count_fw | visitor_count_bw | Total Change | |||
| 20.04.2021 11:05 | 1 | -2 | -1 | |||
| 20.04.2021 12:20 | 1 | -4 | -3 | |||
| 20.04.2021 12:50 | 1 | -3 | -2 | |||
| 20.04.2021 12:55 | 1 | 0 | 1 | |||
| 20.04.2021 13:15 | 1 | 0 | 1 | |||
| 20.04.2021 13:30 | 1 | -3 | -2 | |||
| 20.04.2021 13:45 | 1 | 0 | 1 | |||
| 20.04.2021 14:45 | 1 | 0 | 1 | |||
| 21.04.2021 08:25 | 1 | 0 | 1 | |||
| 21.04.2021 09:20 | 1 | -3 | -2 | |||
| 21.04.2021 09:35 | 1 | 0 | 1 | |||
| 21.04.2021 09:55 | 1 | 0 | 1 | |||
| 21.04.2021 10:10 | 1 | 0 | 1 | |||
| 21.04.2021 10:55 | 1 | 0 | 1 | |||
| 21.04.2021 12:00 | 1 | -2 | -1 | |||
| 21.04.2021 13:15 | 1 | 0 | 1 | |||
| 21.04.2021 13:25 | 1 | 0 | 1 | |||
| 21.04.2021 13:30 | 1 | -2 | -1 | |||
| 21.04.2021 13:35 | 1 | 0 | 1 | |||
| 21.04.2021 14:10 | 1 | -4 | -3 |
Thanks for the help!
Hi Anonymous
It's actually a cumulative total problem. You can create a calculated column with below codes to get the result you want. You need to change the datetime column to Date/time type if it is not.
fill state = CALCULATE(SUM(Samples[Total Change]),FILTER(Samples,Samples[datetime]<=EARLIER(Samples[datetime])))Every time you refresh the data, the calculated column will be calculated again. So in order to make it reset, you need to refresh the data every day.
Hope this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
1 Reply
v-jingzhangCommunity Support
Hi Anonymous
It's actually a cumulative total problem. You can create a calculated column with below codes to get the result you want. You need to change the datetime column to Date/time type if it is not.
fill state = CALCULATE(SUM(Samples[Total Change]),FILTER(Samples,Samples[datetime]<=EARLIER(Samples[datetime])))Every time you refresh the data, the calculated column will be calculated again. So in order to make it reset, you need to refresh the data every day.
Hope this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.