Help: Making a new table to that pulls the last entry for every month from original table

[BCheese]

Hi everyone, just found this community so sorry if I am not doing this correctly. I am a new PBI user and working with this on a project. I have very very little experience in anything outside excel so trying to work with data in PBI is all new to me.
Anyway, I have the table shown below.

I need to create a new table that consolidates the last entry for every month and for every product. I tried the method below but it did not work.

The Product ID is the cell model column if that helps. The error it is returning indicates that the line with YEAR (and MONTH) cannot reference a table column.

Any help is greatly appreciated.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[BCheese]

Hi Ashish, I have solved the original problem but no I have another. The new table (A) has been generated to give the last entry for each month for each product. Now I have tried to filter Table A and create Table B with the most recent entries for each product. So there should just be one entry for each product being the most recent, past value. But something is wrong. This is the code for Table B

So this is mostly correct but it is missing other cell models. There should be 5 entries here. It is only looking at the most recent date, not the most recent date by cell model. I tried setting it up to filter by cell model first but that did not work. 

Table A data

Product ID	Year	Month	lastdate	lastinventoryvalue
A	2024	2	2/26/2024 0:00	207809
A	2023	3	3/27/2023 0:00	989734
A	2024	3	3/11/2024 0:00	102619
A	2023	4	4/24/2023 0:00	941434
B	2023	2	2/21/2023 0:00	224000
B	2024	3	3/28/2024 0:00	448000
B	2024	4	4/29/2024 0:00	320235
C	2023	2	2/27/2023 0:00	149275
C	2024	2	2/16/2024 0:00	97975
C	2023	3	3/27/2023 0:00	358875
C	2024	3	3/28/2024 0:00	85975
C	2023	4	4/28/2023 0:00	402475
C	2024	4	4/12/2024 0:00	67975
D	2023	2	2/21/2023 0:00	0
D	2024	2	2/16/2024 0:00	686990
D	2024	3	3/28/2024 0:00	534400
D	2023	4	4/28/2023 0:00	-2000
D	2024	4	4/8/2024 0:00	329980
E	2023	2	2/21/2023 0:00	221429
E	2024	2	2/23/2024 0:00	147019
E	2024	3	3/28/2024 0:00	146609
E	2023	4	4/28/2023 0:00	251429
E	2024	4	4/26/2024 0:00	133829
F	2023	2	2/27/2023 0:00	251401
F	2024	2	2/16/2024 0:00	259821
F	2023	3	3/27/2023 0:00	207751
F	2024	3	3/28/2024 0:00	190326
F	2023	4	4/27/2023 0:00	182251

I hope this makes sense. Any further help is greatly appreciated. 

BR, Will 

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result.  Should it be a measure or a calculated column formula solution?

[BCheese]

The result for the sample data should be the table:

Product ID	Year	Month	lastdate	lastinventoryvalue
B	2023	2	2/21/2023 0:00	224000
C	2023	2	2/27/2023 0:00	149275
D	2023	2	2/21/2023 0:00	0
E	2023	2	2/21/2023 0:00	221429
F	2023	2	2/27/2023 0:00	251401

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-zhangti · Accepted solution]

Hi, @BCheese 

You can try the following methods.
Sample data:

Table = 
FILTER ( 'OriginalTable',
    [Date] = CALCULATE ( MAX ( 'OriginalTable'[Date] ),
            FILTER ( 'OriginalTable',
                    [Product ID] = EARLIER ( OriginalTable[Product ID] )
                    && [Year] = EARLIER ( OriginalTable[Year] )
                    && [Month] = EARLIER ( OriginalTable[Month] )
            )
        )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[BCheese]

Hi again, ok so I have tried now for a bit to filter down to the most recent entries for each product. But something is wrong. 

So this is mostly correct but it is missing other cell models. There should be 5 entries here. It is only looking at the most recent date, not the most recent date by cell model. I tried setting it up to filter by cell model first but that did not work. 

[BCheese]

Hi! Thanks for the help. This is exactly the correct result that should be achieved! Thanks so much for the help. I just learned a lot about this. That was super easy to just filter a table. Cool. 

Now I have a follow up. I am trying to further filter this table. I need a new table that filters down to just the current inventory for each product, ie. the most recent, earlier date. I will try this now. 

[BCheese]

I have figured out a portion of this but I am still not getting the correct values. The formula I am using now is:

However, it seems to want to return the value for "lastinverntoryvalue" corresponding to the last entry from the original table where the "demand" column is blank. It is not returning the value corresponding to the "lastdate" entry which is what I want.