topic Re: Pull from a table the first transaction based on a date column. in DAX Commands and Tips

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

mak_tushar — Fri, 18 Nov 2022 12:36:42 GMT

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.

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

FreemanZ — Fri, 18 Nov 2022 12:48:28 GMT

Try to create a new table with the code below:

B =

ADDCOLUMNS(

SUMMARIZE (

A[X],

A[Y],

A[Value]

) ,

"Date",

CALCULATE (MIN(A[Date]))

)

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

mak_tushar — Fri, 18 Nov 2022 13:09:34 GMT

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.

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

FreemanZ — Fri, 18 Nov 2022 13:14:08 GMT

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

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

mak_tushar — Fri, 18 Nov 2022 14:32:48 GMT

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. -

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

mak_tushar — Fri, 18 Nov 2022 14:33:51 GMT

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. -

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

FreemanZ — Fri, 18 Nov 2022 16:08:01 GMT

Then try this:

B =

CALCULATETABLE(

ADDCOLUMNS(

SUMMARIZE (

A[X],

A[Y],

A[Value]

) ,

"Date",

CALCULATE (MIN(A[Date]))

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

)

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

mak_tushar — Fri, 18 Nov 2022 16:32:48 GMT

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

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

FreemanZ — Fri, 18 Nov 2022 16:44:24 GMT

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

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

FreemanZ — Fri, 18 Nov 2022 23:35:34 GMT

how does the issue look like?

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

mak_tushar — Sat, 19 Nov 2022 05:01:45 GMT

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.

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

Anonymous — Mon, 21 Nov 2022 06:47:21 GMT

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

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

mak_tushar — Tue, 22 Nov 2022 08:14:18 GMT

Thanks Anonymous, worked perfectly.