DAX expression to get records that have not been updated

[rsrajkumar]

Hi,

For the below table, I would like to know the 'Case Number' that have not been updated for the past 10 days by looking at the 'Modified Date' column .

Case Number	Created Date	Modified Date
15606704	11-Dec-24	11-Dec-24
15611766	10-Dec-24	12-Dec-24
15611766	10-Dec-24	11-Dec-24
15616992	10-Dec-24	23-Dec-24
15617299	12-Dec-24	17-Dec-24
15618894	12-Dec-24	19-Dec-24
15618894	12-Dec-24	12-Dec-24

The result for the above table should be as below for today ie 23-Dec-2024

Case Numbers not updated for the past 10 days

15606704

15611766

Request help on this

Thanks

[SachinNandanwar · Accepted solution]

Not updated for past 10 days = CALCULATE(MAX(Case_Records[Case Number]),FILTER(
        Case_Records,
        NOT(
            Case_Records[Modified Date] >= TODAY() - 10 &&
            Case_Records[Modified Date] <= TODAY()
        )))

[SachinNandanwar · Accepted solution]

Sorry I didnt realise that some case numbers will have update dates that fall both within and outside the 10-day interval.

Here is the calculated table approach

Final_Table=

       Var _tbl=SUMMARIZECOLUMNS(Case_Records[Case Number] ,  FILTER(
        Case_Records,
        (
            Case_Records[Modified Date] >= TODAY() - 10 &&
            Case_Records[Modified Date] <= TODAY()
        )))
        RETURN  
   
  FILTER(
       DISTINCT( SELECTCOLUMNS(Case_Records,Case_Records[Case Number])),
        NOT(Case_Records[Case Number] IN (SELECTCOLUMNS(_tbl, Case_Records[Case Number]))
    ))       

[Kedar_Pande · Accepted solution]

@rsrajkumar 

Create a measure

CasesNotUpdated =
VAR LastModified =
CALCULATE(
MAX(Table[Modified Date]),
ALLEXCEPT(Table, Table[Case Number])
)
RETURN
IF(
DATEDIFF(LastModified, TODAY(), DAY) > 10,
1,
0
)

Use this measure in a visual (e.g., a table or slicer) to filter Case Numbers where the measure equals 1.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[v-tangjie-msft · Accepted solution]

Hi @rsrajkumar ,

First of all thanks to Kedar_Pande for the quick reply and solution. I noticed that you also want to count the total number of records where the case has not been updated for more than 10 days, then you can create another measure.

(1) My test data is the same as yours.

(2) We can create a measure.

Count =
COUNTROWS (
    FILTER (
        SUMMARIZE ( 'Table', [Case Number], "flag", [CasesNotUpdated] ),
        [flag] = 1
    )
)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[rsrajkumar]

Thanks @v-tangjie-msft,@Kedar_Pande , and @SachinNandanwar . All the proposed solution by you works well! 

[Kedar_Pande · Accepted solution]

@rsrajkumar 

Create a measure

CasesNotUpdated =
VAR LastModified =
CALCULATE(
MAX(Table[Modified Date]),
ALLEXCEPT(Table, Table[Case Number])
)
RETURN
IF(
DATEDIFF(LastModified, TODAY(), DAY) > 10,
1,
0
)

Use this measure in a visual (e.g., a table or slicer) to filter Case Numbers where the measure equals 1.

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[rsrajkumar]

@Kedar_Pande Thanks a lot. It works as intended. I would like to count the total records for which the case has not been updated for more than 10 days. While applying a filter for 'CasesNotUpdated' works on the table, it does not allow applying to a Card. 

[SachinNandanwar · Accepted solution]

Not updated for past 10 days = CALCULATE(MAX(Case_Records[Case Number]),FILTER(
        Case_Records,
        NOT(
            Case_Records[Modified Date] >= TODAY() - 10 &&
            Case_Records[Modified Date] <= TODAY()
        )))

[rsrajkumar]

Thanks @SachinNandanwar . This was not working for me. It should not return 15618894 as that record was updated on 19-Dec-2024. It should return 15606704 as it was last modified on 11-Dec-2024

[SachinNandanwar · Accepted solution]

Sorry I didnt realise that some case numbers will have update dates that fall both within and outside the 10-day interval.

Here is the calculated table approach

Final_Table=

       Var _tbl=SUMMARIZECOLUMNS(Case_Records[Case Number] ,  FILTER(
        Case_Records,
        (
            Case_Records[Modified Date] >= TODAY() - 10 &&
            Case_Records[Modified Date] <= TODAY()
        )))
        RETURN  
   
  FILTER(
       DISTINCT( SELECTCOLUMNS(Case_Records,Case_Records[Case Number])),
        NOT(Case_Records[Case Number] IN (SELECTCOLUMNS(_tbl, Case_Records[Case Number]))
    ))       

[rsrajkumar]

Thanks @SachinNandanwar . When there are multiple rows for the same Case Number and some of the Last Updated Dates are more than 10 days, and a few less than 10 days, which part of the DAX expression takes care of this? Am asking this because, my actual table has more records and some of them are not working

[SachinNandanwar]

Its the filter with the NOT IN clause takes care of it

FILTER(
       DISTINCT( SELECTCOLUMNS(Case_Records,Case_Records[Case Number])),
        NOT(Case_Records[Case Number] IN (SELECTCOLUMNS(_tbl, Case_Records[Case Number]))
    ))     

[rsrajkumar]

Thanks a lot @SachinNandanwar . I learnt something new from you on creating tables , and a few new functions!