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

August Community Highlights

Check out a full recap of the month!

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.

Top Solution Authors