Return both the Max and Min value for a date

[saanah2019]

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

[Anonymous]

What determines if you want to see the min or the max?

[saanah2019]

@Anonymous I want to basically filter on the date, so basically create a new column which willl only show me the min and max dates

[v-cherch-msft · Accepted solution]

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,