How do I get the Last Entries for each Item per month?

[blyamoraes]

Hi All, 

I would like to know how I can create a new Table by considering the LAST ENTRY for each item by each month. In this cenario, I just used a few rows from a large table in which we have basically one entry for each item every business day. Sometimes, the item is no longer used, so the last entry should be the last day (As you can see with item 2, which had its last entry on May,3rd). 

Example: For the table below it should show up:
 1 | 30-04-2021 | 50,165.04
 2 | 29-04-2021 |  85,400.46
 3 | 30/04/2021 | 93,419.76
 1 | 10-05-2021 | 50,192.43
 2 | 03-05-2021 | 85,677.70 
 3 | 10-05-2021 | 95,251.63

Table: DAILY_VALUES

Item Date Value
1 23-04-2021 50,144.18
2 15-04-2021 84,659.08
2 16-04-2021 84,852.98
2 19-04-2021 85,141.63
2 20-04-2021 85,359.74
2 23-04-2021 85,034.79
3 15-04-2021 94,407.04
3 16-04-2021 94,629.82
3 19-04-2021 94,995.20
3 20-04-2021 94,187.51
3 23-04-2021 92,685.31
2 27-04-2021 85,540.01
1 27-04-2021 50,152.52
3 27-04-2021 93,454.40
1 29-04-2021 50,160.88
2 29-04-2021 85,400.46
3 29-04-2021 93,538.86
1 30-04-2021 50,165.04
3 30-04-2021 93,419.76
1 01-05-2021 50,169.22
2 01-05-2021 85,677.70
3 01-05-2021 93,419.76
1 03-05-2021 50,169.22
2 03-05-2021 85,677.70
3 03-05-2021 93,151.08
1 04-05-2021 50,173.40
3 04-05-2021 93,661.59
1 05-05-2021 50,177.57
3 05-05-2021 93,661.59
1 06-05-2021 50,181.75
3 06-05-2021 93,862.41
1 07-05-2021 50,187.08
3 07-05-2021 93,716.70
1 10-05-2021 50,192.43
3 10-05-2021 95,251.63

[HotChilli · Accepted solution]

Power Query solution:

Add a Month column :   Date.Month([Date])

Duplicate the query (call it q2)

With q2, Do a GroupBy on Item and Month with aggregation of Max on the Date column.

Merge this table with the original (Inner Join on Item and Date columns) to bring back the value from original table.

Let me know how you get on