Sum of Hourly Values by Date and ID

[Anonymous]

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

[TomMartens · Accepted solution]

Hey @Anonymous ,

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

[Anonymous]

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

[Anonymous]

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! 

[parry2k]

@Anonymous another idea is to use ALLEXCEPT 

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