Forum Discussion
Minimum datediff per unique value
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 |
Hi Anonymous ,
Please follow these steps:
(1) Create a new measureDIFF = DATEDIFF(CALCULATE(MAX('Table'[Date last used]),FILTER(ALL('Table'),'Table'[Cases] = MAX('Table'[Cases]))),TODAY(),DAY)
(2)Final outputBest Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- v-jialluo-msftCommunity Support
Hi Anonymous ,
Please follow these steps:
(1) Create a new measureDIFF = DATEDIFF(CALCULATE(MAX('Table'[Date last used]),FILTER(ALL('Table'),'Table'[Cases] = MAX('Table'[Cases]))),TODAY(),DAY)
(2)Final outputBest Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- AnonymousNot applicable
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!