Reset running total daily

[IMRGZ]

Hi,

After reading a lot of blog posts about this and some questions on the community site, I still seem to not be able to figure this one out.

I am trying to depict weekly/daily (based on what people select) crowd data filtered by data for 2 locations.

The crowd trackers measure by timepoint that someone crosses the entrance and then either add or substract users for that specific timepoint based on the movement, per location.

Now I want to create figure that shows these running totals by day for a selected location and a selected year/week (obligated selection). Then the selection of a specific day in that week is optional.

The data basically comes down to this example:

And the resulting figure that I would like to show is basically this, only turned into a line graph:

I have managed to create a running total and make the figure for one date export, but as soon as I start adding more days, I just do not seem to be able to find the right filters in the running total to reset the running total daily. I have linked the date to a date table btw. (line graph with time on x-axis and running total on y-axis filtered for a location).

Totaal running total in Date = 
CALCULATE(
	SUM('statistics-visits-1598962226601'[Totaal]),
	FILTER(
		ALLSELECTED('statistics-visits-1598962226601'[Date]),
		ISONORAFTER('statistics-visits-1598962226601'[Date], MAX('statistics-visits-1598962226601'[Date]), DESC)
	)
)

Any suggestions?

Thank you in advance.

[mahoneypat]

How did this work out @IMRGZ ?

Regards,

Pat

[IMRGZ]

@mahoneypat Thanks for the help! Summing the data per half hour has indeed done the trick.

[mahoneypat · Accepted solution]

The issue you are seeing is because your Time column has seconds granularity.  With multiple days in the visual, that means many thousands of calculation.  Also, you should delete the DateTime column, as you have done a good thing to split out the Date and Time columns already.  I would round your Times to minutes or even better 5 or 10 minute values for visualization/analysis (and then delete the original Time column too).  I made a 10 min round column to confirm with this column, but the permanent fix should be done in the query editor.

Time in 10 Minutes = TIME(Hour(Data[Time]), ROUNDDOWN(MINUTE(Data[Time]), -1),0)

You can then use a measure like this (or Amit's) to get your desired visual.

RT Time =
VAR vThisTime =
    MIN ( Data[Time in 10 Minutes] )
RETURN
    CALCULATE (
        SUM ( Data[Total] ),
        ALL ( Data[Time in 10 Minutes] ),
        Data[Time in 10 Minutes] <= vThisTime
    )

Regards,

Pat

[IMRGZ]

Dear Icey,

I have created two example data links, the first with the dashboard where I inserted the formula for cumulative data and the second with the data:

https://surfdrive.surf.nl/files/index.php/s/xu7ULYYhgm0HPNf 

https://surfdrive.surf.nl/files/index.php/s/79nmrdbTXWVqf7N 

I have also created a simple example of what I would like to create:

Hope these examples are clear.

Kind regards,

Ivo

[Icey]

Hi @IMRGZ ,

I am curious if you have solved this problem.

If it is solved, can you share your solution with us? Or accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let us know.

Best Regards,

Icey

[IMRGZ]

Hi Icey,

Regrettably I have found no solution yet.

The measure mentioned by @amitchandak  seems to run into trouble when provided with too much data.

I still hope that someone is able to help with this problem.

Kind regards,

Ivo

[Icey]

Hi @IMRGZ ,

Maybe you can share some sample data, not real data, publicly. Then we can do some test for you.

Best regards

Icey

[amitchandak]

@IMRGZ , Can you share your formula? Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

[IMRGZ]

@amitchandak I have forwarded the example pbix file to you in a direct message

[amitchandak]

@IMRGZ , If you want a reset daily and cumulative hourly/time

CALCULATE(
SUM('statistics-visits-1598962226601'[Totaal]),
FILTER(
ALLSELECTED('statistics-visits-1598962226601'[Date]),
'statistics-visits-1598962226601'[Date]= MAX('statistics-visits-1598962226601'[Date])
&& 'statistics-visits-1598962226601'[TIME]= MAX('statistics-visits-1598962226601'[TIME])
)
)

[IMRGZ]

@amitchandak Thank you for your swift reply.

I tried this indeed but I seem to get an error with the time dimension:

"A single value for column 'Time' in table 'statistics-visits-1598962226601' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Does this mean that I should narrow the data down first?