Measure with average of difference between value for max date and value for min date (within groups)

[Anonymous]

Hi, 

I'd like to know how to get with DAX the average difference between the value corresponding to a max date within a group and the value corresponding to a min date within a group. I'll explain this with an example: 

Suppose my data is the following: 

Row_ID	Market	DateHour	Value
1	A	01-09-2022 12:02:12	23
2	A	01-09-2022 12:03:14	25
3	A	01-09-2022 12:05:52	28
4	B	01-09-2022 12:00:03	12
5	B	01-09-2022 12:12:32	15
6	B	01-09-2022 12:23:09	14

What I'd like to obtain is the difference between Value for Max DateHour and Value for Min DateHour within Market (which is my grouping criteria) and, then, get the average of this difference. 

For Market A, Value for Max DateHour is 28, and Value for Min DateHour is 23. Thus, the difference is 5 (Row_ID=2 is discarded because it's not Max DateHour nor Min DateHour)

For Market B, Value for Max DateHour is 14, and Value for Min DateHour is 12. Thus, the difference is 2  (Row_ID=5 is discarded because it's not Max DateHour nor Min DateHour)

And the average would be AVERAGE(5,2)= 3.5. 

How can I get  a measure (not a calculated column) for this in DAX?

Thanks for your help 🙂

[tamerj1 · Accepted solution]

Hi @Anonymous 

if you are using a card visual or slicing by market only then use

Average Difference =
AVERAGEX (
    VALUES ( TableName[Market] ),
    VAR MaxDate =
        CALCULATE ( MAX ( TableName[DateHour] ) )
    VAR MinDate =
        CALCULATE ( MIN ( TableName[DateHour] ) )
    VAR MaxDateValue =
        CALCULATE ( MAX ( TableName[Value] ), TableName[DateHour] = MaxDate )
    VAR MinDateValue =
        CALCULATE ( MIN ( TableName[Value] ), TableName[DateHour] = MinDate )
    RETURN
        MaxDateValue - MinDateValue
)