Pull from a table the first transaction based on a date column.

[mak_tushar]

I have a transaction table(A) with 4 columns say X, Y, Value and Date. I want to create a new table(B) from Table A such that Table B have unique transactions(rows) based on the combination of X and Y columns, and should pick only first transaction based on the date column. For example - 

Table A:

X	Y	Value	Date
X1	Y1	1	11-11-2022
X1	Y1	2	14-11-2022
X2	Y2	4	13-11-2022

Table B: It should only have one row for X1 and Y1 combination for date: 11-11-2022 as it is the first trasaction. 

X	Y	Value	Date
X1	Y1	1	11-11-2022
X2	Y2	4	13-11-2022

Thanks in advance.

[v-yangliu-msft · Accepted solution]

Hi  @mak_tushar ,

Here are the steps you can follow：

1. Create calculated column.

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

2. Create measure.

Flag =
var _select=SELECTEDVALUE('Table 2'[Date])
return
IF(
    MAX('Table'[Invoice Date])=
MINX(FILTER(ALL('Table'),'Table'[X]=MAX('Table'[X])&&'Table'[Y]=MAX('Table'[Y])&&'Table'[Location]=MAX('Table'[Location])&&'Table'[Invoice Date]>=_select),[Invoice Date]),1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

[mak_tushar]

Thanks @v-yangliu-msft, worked perfectly.

[mak_tushar]

This is part of the previous one. Actually Table A has data for past 3 years and I want to have data only after 1 Feb in the new table. - 

[FreemanZ]

Try to create a new table with the code below:

B =

ADDCOLUMNS(

   SUMMARIZE (

       A,

       A[X],

       A[Y],

       A[Value]

    ) ,

   "Date",

   CALCULATE (MIN(A[Date]))

)

[mak_tushar]

Is there a way to apply filter on the date as well? I want to pull records from table A that have invoice date greater than 1 feb.

[FreemanZ]

is it a new requirement or in addition to the previous one?Or could you elaborate the expected table in Excel?

[mak_tushar]

This is part of the previous one. Actually Table A has data for past 3 years and I want to have data only after 1 Feb in the new table. - 

[FreemanZ]

Then try this:

B =

CALCULATETABLE(

ADDCOLUMNS(

   SUMMARIZE (

       A,

       A[X],

       A[Y],

       A[Value]

    ) ,

   "Date",

   CALCULATE (MIN(A[Date]))

),

A[Date]>DATE(2022,1,1)

)

[mak_tushar]

Getting duplicate rows (based on X and Y column).

[FreemanZ]

how does the issue look like?

[FreemanZ]

Is it an issue, considering you have different data in column Value and Date?

[mak_tushar]

First two rows are duplicate as the first three coulmn values are same. Form these two(duplicate) rows, I want only the first row in my table as it has an older date.