cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!!

IDDateTimeValueDistinct Daily Total
A8/3/2019 0:001105
A8/3/2019 1:005105
A8/3/2019 2:003105
A8/3/2019 3:006105
A8/3/2019 4:007105
A8/3/2019 5:003105
A8/3/2019 6:002105
A8/3/2019 7:006105
A8/3/2019 8:001105
A8/3/2019 9:007105
A8/3/2019 10:008105
A8/3/2019 11:003105
A8/3/2019 12:000105
A8/3/2019 13:009105
A8/3/2019 14:004105
A8/3/2019 15:002105
A8/3/2019 16:008105
A8/3/2019 17:002105
A8/3/2019 18:008105
A8/3/2019 19:000105
A8/3/2019 20:003105
A8/3/2019 21:007105
A8/3/2019 22:002105
A8/3/2019 23:008105
B8/3/2019 0:00575
B8/3/2019 1:00275
B8/3/2019 2:00175
B8/3/2019 3:00675
B8/3/2019 4:00775
B8/3/2019 5:00475
B8/3/2019 6:00275
B8/3/2019 7:00175
B8/3/2019 8:00675
B8/3/2019 9:00375
B8/3/2019 10:00175
B8/3/2019 11:00275
B8/3/2019 12:00375
B8/3/2019 13:00175
B8/3/2019 14:00275
B8/3/2019 15:00975
B8/3/2019 16:00475
B8/3/2019 17:00575
B8/3/2019 18:00375
B8/3/2019 19:00175
B8/3/2019 20:00275
B8/3/2019 21:00275
B8/3/2019 22:00175
B8/3/2019 23:00275
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
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:

image.png

 

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

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
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:

image.png

 

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

View solution in original post

Highlighted
Super User VII
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] )
)

 






Did I answer your question? Mark my post as a solution.

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 😞 

 

Helpful resources

Announcements
Upcoming Events

Upcoming Events

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

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

Community Blog

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

Top Solution Authors