cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Reset running total daily

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:

IMRGZ_0-1605607629058.png

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

IMRGZ_1-1605607681387.png

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.

1 ACCEPTED SOLUTION
Super User IV
Super User IV

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
    )
mahoneypat_1-1607904060281.png

 

Regards,
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

11 REPLIES 11
Super User IV
Super User IV

How did this work out @IMRGZ ?

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Super User IV
Super User IV

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
    )
mahoneypat_1-1607904060281.png

 

Regards,
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helper I
Helper I

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:

IMRGZ_0-1607894964148.png

 

Hope these examples are clear.

 

Kind regards,

Ivo

Community Support
Community Support

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

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

Community Support
Community Support

Hi @IMRGZ ,

 

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

 

 

Best regards

Icey

Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Super User IV
Super User IV

@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])
)
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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?

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors