How to check if the value was updated in last 15 days or not

[santoshrnath]

Hello,

I have a table with four columns. Project ID, Project Name, Department and Last Modified by in the Projects table.

There are around 50k rows. I want to check which department did not update in the last 15 days.

[MFelix]

Hi @santoshrnath,

Try to add a measure like:

UPDATED = IF ( Projects[last_modified_date]>= TODAY()-15; "updated";"")

Then use this to make a table and filter by updated.

Regards,

Mfelix

[santoshrnath]

Hi, Thanks for the reply. But it does not work like i wanted. It looks simple but it is bit complicated.

If let us say Project 1 and department A is updated today and the same project was updated 20 days back, it shows Project 1 Department A updated and also not updated because it falls between less than 15 days and greater than 15 days criteria.

If the project and Department is updated within 15 days, it should mark the previous same values updated earlier as something like "not to be considered". So i can filter the not updated project and department.

[Anonymous]

Try creating a new column with something like this, then do the 'Today - 15' check on this column

Last Change Date = CALCULATE(
    max([Change Date]),
    FILTER(
        ALL('Update Table'),
        'Update Table'[Project_ID] = earlier([Project_ID])
        )
)

Where:
[Change Date] is the field with your date of change.
[Project_ID] is your project ID column

'Update Table' is the table containing the update dates

*** EDIT *** Fixed error in code example

[santoshrnath]

I already have the last change Date called Last modifie Date, why should i create a nwe column called last change date?

[Anonymous]

Whoops, made a mistake in that code when i made it more "Genenic" after testing it.

This line:

'Update Table'[Date Updated] = earlier([Date Updated])

Should actually be:

'Update Table'[Project_ID] = earlier([Project_ID])

[Anonymous · Accepted solution]

If you want to get the date on a Project Name basis only, change the filter portion of the code to:

FILTER(
        ALL('Update Table'),
        and(
                'Update Table'[Project_ID] = earlier([Project_ID]),
                'Update Table'[Project_Name] = earlier([Project_Name])
        )
 )

If you want to get it down to the Department & Project name level, just make this change:

FILTER(
        ALL('Update Table'),
        and(
                and(
                        'Update Table'[Project_ID] = earlier([Project_ID]),
                        'Update Table'[Project_Name] = earlier([Project_Name]),
                'Update Table'[Department] = earlier([Department]),
        )
 )

[santoshrnath]

Ross you are Awesome. Thank you so much.

[Anonymous]

Glad to help.

[santoshrnath]

Wow, this is fantastic. Can we do even for departments the same way instead of Project_ID.  I wanted to check it based on Department name.