cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Create Measure with max/min by day

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``````

Super User

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 =
"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.

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors