How to get the total of a duration?

[PowerAutomater]

I have a data source containing events that took place, when they took place (some events took place on multiple dates), and the duration of the event, so something like this:

Event Name	Date	Duration (hrs)
Event 1	1/1/2025	1
Event 2	1/1/2025	0.5
Event 1	2/1/2025	1
Event 3	3/1/2025	3
Event 4	4/1/2025	2
Event 2	5/1/2025	0.5
Event 5	2/1/2025	4

I would like to do a line chart showing the total duration spent on events at a given point in time so essentially totalling the duration. So far I have the Date on the Y axis and the Duration on the X axis. What I'm not sure on is whether to do a sum of the duration or a count or something else to get the right total?

[Ritaf1983 · Accepted solution]

Hi @PowerAutomater 

The type of calculation you need depends on what you're trying to show in the chart.

If your goal is to show how many hours of events occurred on each date, then a simple sum of the Duration column grouped by Date is appropriate.

Example DAX measure:

Total Duration Per Day = SUM('Table'[Duration (hrs)])

Use this measure with Date on the X-axis and the measure on the Y-axis.

If you're trying to show how the total event time accumulates over time (running total), you can use a cumulative sum:

Cumulative Duration = 
CALCULATE(
[Total Duration Per Day],
FILTER(
ALLSELECTED('Table'[Date]),
'Table'[Date] <= MAX('Table'[Date])
)
)

Note: You can group the data by date only to get the total duration per day, or by both date and event name if you want to show the duration per event per day.

Avoid using COUNT unless you're interested in counting how many events occurred, which is different from measuring time.

The pbix with the example is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[PowerAutomater]

It was the first one I was looking for, thank you

[rajendraongole1]

Hi @PowerAutomater  - create a measure for running total line as below: 

Cumulative Duration =
CALCULATE(
SUM('Events'[Duration]),
FILTER(
ALL('Events'[Date]),
'Events'[Date] <= MAX('Events'[Date])
)
)

This works. please check