Calculate Max - Min of a columns for every day and every apartment

[Anonymous]

Hello,

I just started to work with Power Bi and I couldn't find a way to solve my problem.

Here an exemple of my table :

My goal is to get the Max-Min of 'Consommation chauffage (Wh)' for each day and each apartment.

There is apartment I1 but also G1 so I would like a filter on 'Etage', 'Numero appartement' and 'Date'.

[az38]

Hi @Anonymous 

try a measure

Measure = 
var _date = SELECTEDVALUE('Table'[Date])
var _day = DATE(YEAR(_date), MONTH(_date), DAY(_date))

RETURN

CALCULATE(MAX(Table[Consommation chauffage (Wh)]), ALLEXCEPT(Table, Table[Etage], Table[Numero appartement]), DATE(YEAR('Table'[Date]),MONTH('Table'[Date]),DAY('Table'[Date]))=_day) - 
CALCULATE(MIN(Table[Consommation chauffage (Wh)]), ALLEXCEPT(Table, Table[Etage], Table[Numero appartement]), DATE(YEAR('Table'[Date]),MONTH('Table'[Date]),DAY('Table'[Date]))=_day)

it can be much easier but Im not sure about full your data model

[Anonymous]

Hi @az38 

The measure you give me is empty when I try it.

I'm not sure to understand how ALLEXCEPT work exactly.

It says that it return all rows of a table except those affect with the columns specified. But I'm not sure if it does what I really want

Maybe to clarify my purpose here what I want :
The MAX (and MIN) of all 'Consommation chauffage (Wh)' where, for example, : 'Etage' = I AND 'Numero Appartement' = 1 AND 'Date' = 19/08/2019 xx:xx:xx

A IF condition won't be a solution because of the amount of possibilities, of course.

[az38]

@Anonymous 

for me it works pretty good

alexcept() says to powerBI: hey, take all the 'Table' and calculate MIN(Consommation chauffage (Wh)) value for all rows with this Etage and this Numero Appartement

I cannot use the same for the date, because you are using DateTime format and it is different for each rows, so i have to add another special filter for DAY only

[Anonymous · Accepted solution]

OK, Thank you @az38  for your help. I finally manage to do it.

I didn't use your way for the date but I simply use 'Table'[Date].[Year]; 'Table'[Date].[Month]; 'Table'[Date].[Day] in ALLEXCEPT

So I have :

Test = CALCULATE(MAX(Test[Consommation chauffage (Wh)]); ALLEXCEPT(Test; Test[Etage]; Test[Numero appartement]; Test[Date].[Année]; Test[Date].[Mois]; Test[Date].[Jour])) - 
CALCULATE(MIN(Test[Consommation chauffage (Wh)]); ALLEXCEPT(Test; Test[Etage]; Test[Numero appartement]; Test[Date].[Année]; Test[Date].[Mois]; Test[Date].[Jour]))

[az38]

@Anonymous 

I do not like to use these Date part appends because it not always work as i expect. anyway, if its work - thats great!

[Anonymous]

Ok, I understand ALLEXCEPT

But I don't understand why that doesn't work with me..