Return both the Max and Min value for a date

So I have 3 columns in 1 table. The table is Billing and the three columns are Id, Date, Insurance Name.

Id	Date	Insurance Name
100	8/15/2018	Aetna
100	8/16/2018	Aetna
101	8/18/2018	Fidelis
102	8/25/2018	Fidelis
102	8/15/2018	Healthfirst
102	7/15/2018	Healthfirst
102	8/10/2018	Healthfirst
103	8/6/2018	Aetna
103	8/9/2018	Aetna
103	8/15/2018	Healthfirst
104	8/24/2018	Fidelis
104	8/30/2018	Fidelis

I want to only include the values for the max date and min date. I have alot of values for a specific Id, some have like 6 seven fields and I want to do some basic calculations and so only need two date values, the max and min. For examply the original table will now show only these rows:

Id	Date	Insurance Name	max or min
100	8/15/2018	Aetna	min
100	8/16/2018	Aetna	max
101	8/18/2018	Fidelis	max
102	8/25/2018	Fidelis	max

102	7/15/2018	Healthfirst	min

103	8/6/2018	Aetna	min

103	8/15/2018	Healthfirst	max
104	8/24/2018	Fidelis	min
104	8/30/2018	Fidelis	max

Need Help

v-cherch-msft

7 years ago

Hi saanah2019

You may create a measure like below:

Measure =
VAR min_date =
    CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Id] ) )
VAR max_date =
    CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Id] ) )
RETURN
    IF (
        MAX ( Table1[Date] ) = max_date,
        "max",
        IF ( MAX ( Table1[Date] ) = min_date, "min" )
    )

Regards,

Forum Discussion

Return both the Max and Min value for a date

3 Replies

Related content

Measure returning incorrect value

Date Different return wrong value

MAXX Returning duplicate values

Return "Specific value" from table 1, if not found, return value from table 2

Create Measure to Return Highest Value