topic Re: Last date of specific values in DAX Commands and Tips

Last date of specific values

Heleo — Fri, 30 Oct 2020 18:51:51 GMT

Hi everyone,

I have a table with different serial numbers and the date they were used. But I'd like to add a new column with the date of last use, for EACH serial number of my table. I've tried many things I found online, but I either get just the max date, circular dependencies, date duplicates, so on & so on. Nothing works.

Can you help me please?

Example of my table :

Serial number	Date of use
xLg5YcjL	2020-10-22 20:52:10.570
xLg5YcjL	2018-01-16 18:01:05.453
xLg5YcjL	2016-08-16 13:48:24.973
Q9qrVFoV	2020-02-12 13:42:35.190
Q9qrVFoV	2017-06-13 13:57:49.467

What I'd like :

Serial number	Date of use	Date of last use
xLg5YcjL	2020-10-22 20:52:10.570	2020-10-22 20:52:10.570
xLg5YcjL	2018-01-16 18:01:05.453	2020-10-22 20:52:10.570
xLg5YcjL	2016-08-16 13:48:24.973	2020-10-22 20:52:10.570
Q9qrVFoV	2020-02-12 13:42:35.190	2020-02-12 13:42:35.190
Q9qrVFoV	2017-06-13 13:57:49.467	2020-02-12 13:42:35.190

Many thanks for your help !!

Re: Last date of specific values

AlB — Fri, 30 Oct 2020 19:10:52 GMT

Hi Heleo

Try this for a calculated column in your table

Date of last use = CALCULATE ( MAX ( Table1[Date of use] ), ALLEXCEPT ( Table1, Table1[Serial number] ) )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Last date of specific values

camargos88 — Fri, 30 Oct 2020 19:12:44 GMT

Hi Heleo ,

You can create a column like:

Last Date = CALCULATE(MAX('Table'[Date of use]), ALLEXCEPT('Table', 'Table'[Serial number]))

Re: Last date of specific values

Heleo — Sat, 31 Oct 2020 08:24:27 GMT

Thank you a lot guys! This solution worked perfectly!