Last non blank help

[Alex_Bartlett]

Hi All,

Hoping someone can help give me some advice on this one. I have done some research but can't quite work out the correct way to use LastNonBlank. I have two tables, one that lists products and when work has been done with them, and another table that is going to be used as a dataset for a report. 

Table 1 contains some key dates I need based on a product ID however that product ID can appear multiple times.

Example of what this table looks like below:

Product ID	DateCreated	DateCompleted
54179	28/07/2022	17/08/2022
711133	31/07/2022	17/08/2022
801371	01/08/2022	19/08/2022
801371	01/08/2022	23/08/2022
711133	01/08/2022
801371	03/08/2022	24/08/2022
102333	06/08/2022	14/08/2022
102333	06/08/2022	14/08/2022
704112	10/08/2022	15/08/2022

What I need is to pull out the last date where something was completed for this product. Other dates for each product can be ignored so I think LASTNONBLANK should give this to me but I need to select the last completion date for each unique product ID.

In my second table I have the unique Product IDs and I want to eventually get the last date that something was completed added to that table as a lookup. What I want to end up with is something like the below:

Product ID	DateCompleted
54179	17/08/2022
711133	17/08/2022
801371	19/08/2022
102333	14/08/2022
704112	15/08/2022

Is anyone able to help me with this? 

Thank you in advance for your help!

[Anonymous · Accepted solution]

Hi  @Alex_Bartlett ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:

Measure =
CALCULATE (
    MAX ( 'Table'[DateCompleted] ),
    FILTER ( 'Table', NOT ( ISBLANK ( 'Table'[DateCompleted] ) ) )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@Alex_Bartlett , In case you need a new table

Summarize(filter(Table, not(isblank(Table[Date])) ), [Product ID] , "DateCompleted", max(Table[DateCompleted]) )

or a measure

maxx(filter(Table, not(isblank(Table[Date])) ), Table[DateCompleted])