Get a list of tasks that have changed its status within the last week

[paulinasaez]

Hi everyone,

I need to get a list of tasks that were marked as completed within the last week. 

I take a snapshot of a tasks database every week, let's call them database1 (last week snapshot) and database2 (current week snapshot). Both databases have these columns: [Task name], [Status]

So, database1 (last week's) would have less amount tasks (less amount of rows because every day new tasks are added) and less tasks marked as completed than database2 (current week).

I've seen tutorials on comparisons but they're either for calculating an amount that's easily summed (like last year and current year sales) or work with transacional data, not "snapshots" of databases like I do. 

Any help will be greatly appreciated.

Thanks,

Paulina

[Dhacd]

Hi @danextian,

Are you using powerquery to bring the data to power bi. And if there is a key for all the tasks this can be achieved easily. 

Please provide a sample data set of db1 and db2.

Thanks and regards,
Atma.

[paulinasaez]

Yes I'm using power query

The only key in Notion AFAIK is the name of the row (in this case, the task's name)

Here's some sample data:
For database1:

Task	Status
1. task 1	In Progress
2. task 2	In Progress

For database 2:

Task	Status
1. task 1	In Progress
2. task 2	Completed
3. task 3	Next up

[Dhacd]

@paulinasaez ,

I believe you are appending the data to the existing table on a weekly basis right? Or Creating a new a new table for each snapshot?

Thanks and regards,

Atma.

[paulinasaez]

I'm downloading the whole tasks database "as is", once a week. 

I'm not trasnforming/appending/merging anything yet as I don't know what to do.

[Dhacd]

@paulinasaez I believe the best architecture to use here keep all the excel files in a folder and use powerquery to load the data by appending the tables each week and renaming the table name with the week number or date you are downloading the data. Then keep the name of the table as a column inside the powerquery. After that, we can write a Conditional column to check the status. 

Regards,
Atma.

[danextian]

So with the given data, Task2 is the one completed in the current week from last week? If so, you can create a calculated column that looks up the current week's task in in last week's. 

CALC COLUMN =
VAR __LOOKUP =
    LOOKUPVALUE ( database1[task], database1[task], database2[task] )
RETURN
    IF ( ISBLANK ( __LOOKUP ), "RESULT1", "RESULT2" )

[paulinasaez]

I need to be able to find out which tasks were completed, just by comparing both datasets. 

I have hundreds of tasks.

Do your solution needs for me to input each task manually within the LOOKUPVALUE function? If that's the case, that wouldn't be possible.

[danextian]

Hi @paulinasaez ,

Doesn't your database have  a column that indicates when a task was completed? If it does, that can column can possibly be used.

[paulinasaez]

No it doesn't have a completed date/time column.
I'm handling a dataset that's built in Notion and it doesn't have this capability.