cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Sum of Hourly Values by Date and ID

Hello,

I am trying to find the measure that will sum the hourly values for each distinct ID by the date.

Here is the model and desired output in last column. How can I get the Distinct Daily Total? Thanks!!

 ID DateTime Value Distinct Daily Total A 8/3/2019 0:00 1 105 A 8/3/2019 1:00 5 105 A 8/3/2019 2:00 3 105 A 8/3/2019 3:00 6 105 A 8/3/2019 4:00 7 105 A 8/3/2019 5:00 3 105 A 8/3/2019 6:00 2 105 A 8/3/2019 7:00 6 105 A 8/3/2019 8:00 1 105 A 8/3/2019 9:00 7 105 A 8/3/2019 10:00 8 105 A 8/3/2019 11:00 3 105 A 8/3/2019 12:00 0 105 A 8/3/2019 13:00 9 105 A 8/3/2019 14:00 4 105 A 8/3/2019 15:00 2 105 A 8/3/2019 16:00 8 105 A 8/3/2019 17:00 2 105 A 8/3/2019 18:00 8 105 A 8/3/2019 19:00 0 105 A 8/3/2019 20:00 3 105 A 8/3/2019 21:00 7 105 A 8/3/2019 22:00 2 105 A 8/3/2019 23:00 8 105 B 8/3/2019 0:00 5 75 B 8/3/2019 1:00 2 75 B 8/3/2019 2:00 1 75 B 8/3/2019 3:00 6 75 B 8/3/2019 4:00 7 75 B 8/3/2019 5:00 4 75 B 8/3/2019 6:00 2 75 B 8/3/2019 7:00 1 75 B 8/3/2019 8:00 6 75 B 8/3/2019 9:00 3 75 B 8/3/2019 10:00 1 75 B 8/3/2019 11:00 2 75 B 8/3/2019 12:00 3 75 B 8/3/2019 13:00 1 75 B 8/3/2019 14:00 2 75 B 8/3/2019 15:00 9 75 B 8/3/2019 16:00 4 75 B 8/3/2019 17:00 5 75 B 8/3/2019 18:00 3 75 B 8/3/2019 19:00 1 75 B 8/3/2019 20:00 2 75 B 8/3/2019 21:00 2 75 B 8/3/2019 22:00 1 75 B 8/3/2019 23:00 2 75
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

## Re: Sum of Hourly Values by Date and ID

Hey @kbig02 ,

first I created a calculated column using this DAX statement to extraxt the day part from the DateTime column:

`Date = DATE(YEAR('Table1'[DateTime]) , MONTH('Table1'[DateTime]) , DAY('Table1'[DateTime]))`

Then, I created a measure using this DAX statement:

```Measure =
SUMX(
VALUES(Table1[ID])
, var maxDateTime = MAX('Table1'[DateTime])
var maxDate = DATE(YEAR(maxDateTime) , MONTH(maxDateTime) , DAY(maxDateTime))
return
CALCULATE(
SUM(Table1[Value])
, ALL('Table1'[DateTime] , 'Table1'[Date] , 'Table1'[Value])
, 'Table1'[Date] = maxDate
)
)```

This allows to create a table visual like so:

Hopefully this is what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
4 REPLIES 4
Highlighted
Super User IV

## Re: Sum of Hourly Values by Date and ID

Hey @kbig02 ,

first I created a calculated column using this DAX statement to extraxt the day part from the DateTime column:

`Date = DATE(YEAR('Table1'[DateTime]) , MONTH('Table1'[DateTime]) , DAY('Table1'[DateTime]))`

Then, I created a measure using this DAX statement:

```Measure =
SUMX(
VALUES(Table1[ID])
, var maxDateTime = MAX('Table1'[DateTime])
var maxDate = DATE(YEAR(maxDateTime) , MONTH(maxDateTime) , DAY(maxDateTime))
return
CALCULATE(
SUM(Table1[Value])
, ALL('Table1'[DateTime] , 'Table1'[Date] , 'Table1'[Value])
, 'Table1'[Date] = maxDate
)
)```

This allows to create a table visual like so:

Hopefully this is what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Super User VII

## Re: Sum of Hourly Values by Date and ID

@kbig02 another idea is to use ALLEXCEPT

```Daily Value =
CALCULATE(
SUM( 'Table'[Value] ),
ALLEXCEPT( 'Table', 'Table'[ID] )
)```

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Frequent Visitor

## Re: Sum of Hourly Values by Date and ID

Thanks. I had to delete a line of code

`, 'Table1'[Date] = maxDate`

but it worked after I deleted. Not sure what that does exactly.. but it threw an error for me. Thanks again!

Highlighted
Frequent Visitor

## Re: Sum of Hourly Values by Date and ID

@TomMartens any idea on how to get 95th percentile by month for each data group?

I have tried

95th in = PERCENTILEX.INC(Interfaces, [test], 0.95)

but no luck 😞

Announcements

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

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

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors
Top Kudoed Authors