Latest Date

[Anonymous]

Hi All,

I have a table with the structure below:

Process Number	Date	Department Description
354152	05/10/2019 10:24:20 AM	IT
354152	05/10/2019 10:29:10 AM	Business
354152	05/10/2019 10:32:28 AM	Business
354152	06/10/2019 11:12:17 AM	Business
397814	07/11/2019 08:01:20 AM	IT
397814	07/11/2019 09:11:24 AM	IT
397814	07/11/2019 09:19:20 AM	HR
397814	07/12/2019 08:01:20 AM	HR

What I want is to get the latest date of specific process number for each department. 

For example:

for process number 354152 the latest date in Business that is 06/10/2019 11:12:17 AM.

for process number 397814 the latest date in IT that is 07/11/2019 09:11:24 AM, the latest date in HR that is 07/12/2019 08:01:20 AM.

I have tried to use the MAXX function but it gives me the latest date for entire process and I want the latest date for specific department.

Thanks

[Melvlee]

Hi @Anonymous ,

You can use GroupBy and Add Index in power query.

please reference this article https://www.poweredsolutions.co/2019/07/30/grouping-rows-with-power-bi-power-query/ 

If you have your question answered, please give me a kudo.

Thanks and regards.

[AntrikshSharma · Accepted solution]

Like this?

Column =
CALCULATE (
    MAX ( Jen[Date] ),
    ALLEXCEPT ( Jen, Jen[Process Number], Jen[Department Description] )
)

[Anonymous]

Hi @AntrikshSharma ,

What if I have a one more column with the name of the user like:

Process Number, Date, Department Description, User name.

Because now it appears the name of the users who was involved in the process with the same Max Date I want only the last user with the max date.

[AntrikshSharma]

Try something like this, I have used CONCATENATEX in case if there are duplicates in your table it can be concatenated.

Column =
VAR ProcessNum = Jen[Process Number]
VAR Dept = Jen[Department Description]
VAR F =
    FILTER (
        ALL ( Jen ),
        Jen[Process Number] = ProcessNum
            && Jen[Department Description] = Dept
    )
VAR MaxDate =
    CALCULATE ( MAX ( Jen[Date] ), F )
VAR UsersWithMaxDate =
    SELECTCOLUMNS ( FILTER ( F, Jen[Date] = MaxDate ), "Users", Jen[User] )
VAR NumOfUser =
    COUNTROWS ( UsersWithMaxDate )
VAR Result =
    IF (
        NumOfUser > 1,
        CONCATENATEX ( UsersWithMaxDate, [Users], ", " ),
        UsersWithMaxDate
    )
RETURN
    Result

[Anonymous]

Thank you Antriksh, it worked but it shows multiple lines with the last user and what I wanted is just a single line with the latest date, process number, department, user.

There is no other way to do that?

[AntrikshSharma]

try this:

Column =
VAR MaxDate =
    CALCULATE (
        MAX ( Jen[Date] ),
        ALLEXCEPT ( Jen, Jen[Process Number], Jen[Department Description] )
    )
VAR UserWithMaxDate =
    SELECTCOLUMNS (
        FILTER ( ALL ( Jen[Date], Jen[User] ), Jen[Date] = MaxDate ),
        "Users", Jen[User]
    )
RETURN
    UserWithMaxDate

[Anonymous]

Hi,

it gives me the error below

[AntrikshSharma]

It worked for me even with duplicates, can you please show how your updated date looks like with users.

[Anonymous]

Hi,

DateMax code:

Column =
CALCULATE (
    MAX ( Jen[Date] ),
    ALLEXCEPT ( Jen, Jen[Process Number], Jen[Department Description] )
)

Column code:

Column =
VAR ProcessNum = Jen[Process Number]
VAR Dept = Jen[Department Description]
VAR F =
    FILTER (
        ALL ( Jen ),
        Jen[Process Number] = ProcessNum
            && Jen[Department Description] = Dept
    )
VAR MaxDate =
    CALCULATE ( MAX ( Jen[Date] ), F )
VAR UsersWithMaxDate =
    SELECTCOLUMNS ( FILTER ( F, Jen[Date] = MaxDate ), "Users", Jen[User] )
VAR NumOfUser =
    COUNTROWS ( UsersWithMaxDate )
VAR Result =
    IF (
        NumOfUser > 1,
        CONCATENATEX ( UsersWithMaxDate, [Users], ", " ),
        UsersWithMaxDate
    )
RETURN
    Result

[AntrikshSharma]

sorry Jen looks like I am confused, can you share again what the issue is?

[Anonymous]

The issue is that it shows two lines instead of one with the latest date and with the latest user. When I try to use the last code you sent i have an error below

[Anonymous]

Thank you very much @AntrikshSharma .

[AntrikshSharma]

Great! You're welcome!