Forum Discussion

Alex_Bartlett's avatar
Alex_Bartlett
Frequent Visitor
4 years ago
Solved

Last non blank help

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 IDDateCreatedDateCompleted
5417928/07/202217/08/2022
71113331/07/202217/08/2022
80137101/08/202219/08/2022
80137101/08/202223/08/2022
71113301/08/2022 
80137103/08/202224/08/2022
10233306/08/202214/08/2022
10233306/08/202214/08/2022
70411210/08/202215/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 IDDateCompleted
5417917/08/2022
71113317/08/2022
80137119/08/2022
10233314/08/2022
70411215/08/2022

 

Is anyone able to help me with this? 

Thank you in advance for your help!

 

DAX
LASTNONBLANK
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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])

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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