# Beast Mode to compare the record from the recent Monday to the last Monday

⚪️
edited July 19

Hi All,

I have a dataset that has the the different schedule codes by date at a PO number level..I am trying to identify the records from the most recent monday and compare it to the immediate last monday and then flag the combination of (POnumber /schedule_code) to be old (meaning this combination existed the last monday otherwise I have to flag it as new.. I have to consider only the most recent monday and the immediate last monday for consideration

Input table

Output table

In the above case 07/18 is the most recent monday and 07/11 is the immediate last monday for comparison...Also attached the excel sheet data

Tagged:

• I would do this in an ETL by filtering your dataset to this monday, and then using a second branch, filtering to last monday. Then you can right join last monday to this monday on date, po number, and schedule code. You can use a formula tile and set it to "new" when the left side po number is empty, and "old" when the left side po number is populated.

• Indiana 🥷

You can utilize a formula to calculate the most recent Monday for the week:

````Date` - INTERVAL (CASE WHEN DAYOFWEEK(`Date`) = 1 THEN 6 ELSE (DAYOFWEEK(`Date`) - 2) END) DAY
```

A week prior:

````Date` - INTERVAL (CASE WHEN DAYOFWEEK(`Date`) = 1 THEN 6 ELSE (DAYOFWEEK(`Date`) - 2) END) DAY - INTERVAL 7 DAY
```

`DAYOFWEEK` returns a number between 1 (Sunday) and 7 (Saturday).

The `CASE` statement is checking if it's a Sunday to subtract 6 days instead of 1.

`-2` is to reduce the days to subtract to get to Monday. For example Tuesday (3) we need to subtract only 1 day instead of 3 days.

You can then filter your data where the date and the Monday date match. Twice - once for this week, once for last week.

To populate the New vs Old use a Formula tile as @mhouston recommended:

```CASE WHEN `Left PO Column` IS NULL THEN 'New' ELSE 'Old' END
```