Create Measure with max/min by day

[Atiroocky]

Hello,

I would like to create a Measure which can calculate min/max of a table by day, then do some calculations.
The measure will call a parameter, so it is dynamic. That is why I need a measure not a calculated column.

I have a table like this, with a date/time column, and value.

I managed to do calculate min and max values per day, directly with a calculated column, but it is not dynamic.

here is the formula :

Tx = CALCULATE (
MAX ( T_2017[T°C] ),
FILTER (T_2017, T_2017[Date].[Jour] = EARLIER ( T_2017[Date].[Jour] ) && T_2017[Date].[Mois] = EARLIER ( T_2017[Date].[Mois] )&& T_2017[Date].[Jour] = EARLIER ( T_2017[Date].[Jour] ))
) 

So I need a measure which will do :

For each day I have to calculate max(T°C) ("Tx") and min(T°C) ("Tn")  then do the average ("S" = (Tn+Tx)/2)
For each day, I compare the average value ("S") to my parameter  , and return a calculated value like this

 DJU = if (param-S>0 , param-S , 0)
Then I need to sum all the DJU

I tried something like this, but it returns 0.

DJU_J =
var Tx =
CALCULATE(
max(T_2017[T°C]),T_2017[Date].[Année]=T_2017[Date].[Année],T_2017[Date].[Mois]=T_2017[Date].[Mois],T_2017[Date].[Jour]=T_2017[Date].[Jour])

Var Tn =
CALCULATE(
min(T_2017[T°C]),T_2017[Date].[Année]=T_2017[Date].[Année],T_2017[Date].[Mois]=T_2017[Date].[Mois],T_2017[Date].[Jour]=T_2017[Date].[Jour])

var s = (Tn+tx)/2

var DJU = if(S-T_int_ref[Valeur T_int_ref]>0,S-T_int_ref[Valeur T_int_ref],0)

return DJU

Thanks for your help.

[some_bih]

Hi @Atiroocky , I insert your data in my Excel BUT with Date format import in DAX table, not format date time as you wrote request is data on day level (not day and hours). 2 measures created (adjust Sheet1 to your table name):

1. M_Max_TC = MAX(Sheet1[T_C_ Value])

2. M_Min_TC = MIN(Sheet1[T_C_ Value])

and NEW table is created with DAX (as shown on picture below: 

NewTable =

ADDCOLUMNS(values(Sheet1[Date]),

    "Max",[M_Max_TC],

    "Min",[M_Min_TC],

    "S",ROUND( ([M_Max_TC]+[M_Min_TC])/2,2)

)

 I could not find your parameter info. I hope this help.