topic Re: First value by date in DAX Commands and Tips

First value by date

astronauta49 — Thu, 08 Apr 2021 17:40:04 GMT

Hello. Can you help me, please ?

I need a table with the same column, with unique (not duplicate) values from IDU column ... filtered by DATE column.

I need the first row (date) of each IDU.

Thanks a lot

IDU	DATE	TRIM
1	2/04/2021	AAAA
2	6/04/2021	AAAA
2	10/04/2021	BBB
2	14/04/2021	BBB
3	18/04/2021	CCC
4	22/04/2021	CCC

Re: First value by date

amitchandak — Fri, 09 Apr 2021 04:21:08 GMT

astronauta49 , Create a new measure for TRIM and use min for date if needed

new TRIM =
VAR __id = MAX ('Table'[Name] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Name] = __id )
CALCULATE ( max ('Table'[TRIM] ), VALUES ('Table'[Name] ),'Table'[Name] = __id,'Table'[Date] = __date )

Re: First value by date

astronauta49 — Sun, 18 Apr 2021 18:05:46 GMT

Sorry, it took me a long time to reply. Thank you very much for your answer. I have not solved my problem yet. I don't understand the code. Do you need to put it in a new measure or column? I'll keep doing tests. Thanks

Re: First value by date

daxer-almighty — Sun, 18 Apr 2021 23:04:47 GMT

astronauta49

Here's the calculated table you want:

[New Table] = // This is a calculated table // Let the table from your initial post // be called T (you just substitute the // name you've got in your model). var IDUsWithFirstDates = ADDCOLUMNS( DISTINCT( T[IDU] ), "@FirstDate", CALCULATE( MIN( T[Date] ) ) ) var Result = CALCULATETABLE( T, TREATAS( IDUsWithFirstDates, T[IDU], T[DATE] ) ) RETURN Result

Re: First value by date

astronauta49 — Fri, 23 Apr 2021 17:11:05 GMT

thank you so much !!