Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Latest Date

Hi All,

 

I have a table with the structure below:

 

Process NumberDateDepartment Description
35415205/10/2019 10:24:20 AMIT
35415205/10/2019 10:29:10 AMBusiness
35415205/10/2019 10:32:28 AMBusiness
35415206/10/2019 11:12:17 AMBusiness
39781407/11/2019 08:01:20 AMIT
39781407/11/2019 09:11:24 AMIT
39781407/11/2019 09:19:20 AMHR
39781407/12/2019 08:01:20 AMHR

 

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

Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    6 years ago

    Like this?

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

13 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    Like this?

    Column =
CALCULATE (
    MAX ( Jen[Date] ),
    ALLEXCEPT ( Jen, Jen[Process Number], Jen[Department Description] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        AntrikshSharma
        Community Champion
        6 years ago

        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