Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Minimum datediff per unique value

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

 

CasesDoc numbers

Date last used 

A11

12-12-22

A22

09-12-22

A33

01-02-22

B41

11-12-22

B54

10-12-22

C63

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:

CaseMinimum date diff
A1
A1
A1
B2
B2
C8
  • 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.

2 Replies

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support

    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.

    • Anonymous's avatar
      Anonymous
      Not 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!