Measure: Calculate Category conditioned and Filtered Values

[Anonymous]

Hello,

I am struggling getting a measure to work.

Could you please help me.

I would like to create the following measure:

Find the largest Timestamp. => 27.07.2019 00:17

Get the corresponding category. => Z

Sum up all values for this category Z. => 3

Find the Minimum Timestamp for category Z. => 26.07.2019 20:17

Calculate time diffrence. => 4h

Divide the sumed values by the time difference. => 3/4

Timestamp	Category	Value
26.07.2019 13:17	X	0
26.07.2019 14:17	X	1
26.07.2019 15:17	X	1
26.07.2019 16:17	X	1
26.07.2019 17:17	Y	0
26.07.2019 18:17	Y	0
26.07.2019 19:17	Y	0
26.07.2019 20:17	Z	0
26.07.2019 21:17	Z	1
26.07.2019 22:17	Z	1
26.07.2019 23:17	Z	1
27.07.2019 00:17	Z	0

Could anyone help me with this. I searched a lot for DAX Functions CALCULATE and FILTER but i could not find a solution.

Than kyou very much in advance.

Best regards

Sebastian

[Anonymous]

 -- If your table is big, then you should not store
-- timestamps in one column. You should have separate
-- columns for the date and for the time. The reason
-- being that such a column would be dense (big number
-- of unique values) and as such it would not be well
-- compressed, hence calculations involving it would
-- be slow out of necessity.


[Measure] =
var __maxTimestamp = MAX( T[Timestamp] )
var __maxTimestampCategory =
    -- this should return a single value
    -- if not, then you might have data quality
    -- problems or your algorithm does not take
    -- into account all possibilities
    CALCULATE(
        VALUES( T[Category] ),
        T[Timestamp] = __largestTimestamp
    )
var __minTimestampForCategory =
    CALCULATE(
        MIN ( T[Timestamp] ),
        T[Category] = __maxTimestampCategory
    )
var __timeDiffInHours =
    DATEDIFF (
        __minTimestampForCategory,
        __maxTimestamp,
        HOUR
    )
var __sum =
    CALCULATE(
        SUM ( T[Value] ),
        T[Category] = __largestTimestampCategory  
    )
var __result =
    DIVIDE( __sum, __timeDiffInHours )
return
    __result

Try this measure. It might or might not do what you want depending on your intentions which are not fully clear at this moment. For instance, what should happen when you slice the data by some attributes?

Best

Darek