Calculate or Measure Min and Max data to get interval

[Anonymous]

i really confused with this case i need min and max data SMU Down to get interval. how about that?

Unit Number	Min Down SMU	Max SMU	Interval
CE4223	106.9	20.730

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Input Data

EquipmentID	DownSMU
CE4223	106,9
CE4223	8483,2
CE4223	13673,1
CE4223	13761,1
CE4223	13805
CE4223	13805
CE4223	13805
CE4223	13805
CE4223	13809,4

Output Data

EquipmentID	Min	Max	Interval
CE4223	106,9	13809,4	13702,5

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculate column or a measure to meet your requirement.

1. The measure and result like this,

Interval = 
var min_ = CALCULATE(MIN('Table'[DownSMU]),FILTER(ALLSELECTED('Table'),'Table'[EquipmentID]=MAX('Table'[EquipmentID])))
var max_ = CALCULATE(MAX('Table'[DownSMU]),FILTER(ALLSELECTED('Table'),'Table'[EquipmentID]=MAX('Table'[EquipmentID])))
return
max_ - min_

2. Or we can create a calculate column,

Column = 
var min_ = CALCULATE(MIN('Table'[DownSMU]),FILTER('Table',EARLIER('Table'[EquipmentID])='Table'[EquipmentID]))
var max_ = CALCULATE(MAX('Table'[DownSMU]),FILTER('Table',EARLIER('Table'[EquipmentID])='Table'[EquipmentID]))
return
max_ - min_

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.