Minimum datediff per unique value

Anonymous — Tue, 13 Dec 2022 15:47:35 GMT

Let's say i've got a couple of relevant columns:

Cases	Doc numbers	Date last used
A	11	12-12-22
A	22	09-12-22
A	33	01-02-22
B	41	11-12-22
B	54	10-12-22
C	63	05-12-22

Each case consists of a number of documents (could be 1 could be 100 etc). For each doc number i have a Date last used.

I calculated the difference between the last time a document was used and today.

The problem is: I want a column added where for each case (so A, B and C etc) i see what the datediff is for the LAST used document in that case. How do I do this?

Edit: Explaination what i want with it:

It might help if you know why I want to do this: I want to generate a visualisation table with al cases where the datediff of the last used docname is bigger than a certain number. For instance I want to see al the cases A t/m C where the datediff between last used date and today is 180 and all the cases D t/m Z where this datediff is 230.
I made a column that looks Yes/no = IF([datediff between date last updated and today]>[column with 180 for case A-C and 230 for case D-Z], "Yes", "no").
This column i want to use to filter my page. I only want to see the cases where the Yes/no column gives Yes, because this are the cases where datedifference from the last used docname is larger than it should be.

BUT when i do it like this it gives me al the docnames within a case where the datediff surpasses 180 or 230 (for A and B). I don't want to know it per document, i want to know if the datediff of the LAST used docname in a case is higher than it should be..

For example:

Case	Minimum date diff
A	1
A	1
A	1
B	2
B	2
C	8

Re: Minimum datediff per unique value

v-jialluo-msft — Wed, 14 Dec 2022 03:02:55 GMT

Hi Anonymous ,

Please follow these steps:
(1) Create a new measure

DIFF = DATEDIFF(CALCULATE(MAX('Table'[Date last used]),FILTER(ALL('Table'),'Table'[Cases] = MAX('Table'[Cases]))),TODAY(),DAY)

(2)Final output

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

Re: Minimum datediff per unique value

Anonymous — Wed, 14 Dec 2022 08:44:53 GMT

You are the best! Added it as a meassure and used the meassure to calculate if a certain case is longer inactive than I want it to be and now it works! Thank you very much and have a nice day!

topic Re: Minimum datediff per unique value in DAX Commands and Tips

Minimum datediff per unique value

Re: Minimum datediff per unique value

Re: Minimum datediff per unique value