How to create table based on minimum date from other table

[Anonymous]

Hi Folks,

I want to create second table from first table using filters with dates and other variables as follows. How to create this ?

Follwowing is the expected table and original table

	Original Table
	Index	Category	Value_1	Value_2	Date
	1	A	0.06	1.00	1/3/2019	Minimum Date
	2	A	-0.1	4.00	1/4/2019
	3	A	0.03	58.00	1/5/2019
	4	A	0.1	63.00	1/6/2019
	5	D	0.02	64.26	1/3/2019
	6	D	-0.02	62.97	1/4/2019
	7	D	-0.09	57.31	1/5/2019
	8	D	0.04	59.60	1/6/2019
	DESIRED TABLE
	Index	Category	Value_1	Value_2	Date
	1	A	0.06	1.00	1/3/2019
	5	D	0.02	1.02	1/3/2019

[Anonymous]

go to ..> Model --> choose --> new table,

As per your requirement, use the below formula to create a table.

Table = CALENDAR(MIN('Earlier'[Date]), MAX('Earlier'[Date]) )

[parry2k]

@Anonymous you don't need new table for this you can achieve this by measure, one thing is not clear from your exmple, why value_2 for D will be 1.02 instead of 64.26 from table A

[Anonymous]

Yeah, it is 64.26. Actually, I want to utilize this table for joining purpose hence keeping it in the seperate table. How can I do it? 

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may use the following DAX to add a calculated table.

Table =
FILTER (
    Table1,
    RANKX (
        FILTER ( Table1, Table1[Category] = EARLIER ( Table1[Category] ) ),
        Table1[Date],
        ,
        ASC,
        DENSE
    ) = 1
)

[Anonymous]

Thanks for your help. I will try it and let you know if some issue.