Showing latest date in a mixed column with both date and text values

[ld17]

I have a column called "completion date" in my Power Bi report that contains both text and date values, and once these are placed on a matrix visual, I want to display the latest completion dates.  My source file is a SharePoint folder, and every week, I have a new training report that gets uploaded, and the latest training completion dates pull from there. So at one point, a user may be "in progress" on a training, and then next time ther report pulls, they may be "complete" on the training (matrix would then display the date completed).

If the user is not yet complete, I want the current status to show, such as "in progress," "registered," etc. The issue is that because there are text values in the column, I cannot convert it to a date column. If I try to do so, I get an error message saying, "We can't automatically convert the column to date type" and therefore, the latest date doesn't pull since it doesn't recognize the column as a date column.

It seems that my dates and text values need to be in separate columns somehow, but I believe I would still need a DAX expression that joins them. What do I need to do to achieve this? Thanks in advance!

[BA_Pete]

Hi @ld17 ,

In Power Query, create a new custom column like this:

try
    if Value.Is(Date.From([completion date]), type date) then [completion date] else null
otherwise null

This will split out your date values into a new column. You can then write measures to pick out date/text values using logic such as:

IF( ISBLANK( yourTable[newColumn]), then...    etc.

Pete

[ld17]

Thank you so much for the response. Part of the solution was to create a new column, but it ended up being a multi-step process to achieve the result I wanted. The steps were as follows:

1.) Duplicate my table

2.) Using the duplicated table, group my rows using MAX as my operation and name my new column "Latest Completion Date."

3.) Sort "Latest Completion Date" in descending order.

4.) Use the CONCATENATE function to join my name and training title column and create a new column so I could remove any duplicates and keep only the latest value.

5.) Add another column in my table and use the following DAX expression to get my statuses and dates in one column:

Transcript Status = 

IF(ISBLANK([Latest Completion Date]), 

IF(OR( 

[Transcript - Transcript Status] = "In Progress", 

OR( 

[Transcript - Transcript Status] = "Registered", 

[Transcript - Transcript Status] = "In Progress / Past Due" 

) 

), 

FORMAT([Latest Completion Date], "MM/dd/yyyy"), 

"Not Assigned" 

), 

IF([Transcript - Transcript Status] = "Completed", 

FORMAT([Transcript - Transcript Status], "MM/dd/yyyy"), 

IF(OR( 

[Transcript - Transcript Status] = "In Progress", 

OR( 

[Transcript - Transcript Status] = "Registered", 

[Transcript - Transcript Status] = "In Progress / Past Due" 

) 

), 

FORMAT([Latest Completion Date], "MM/dd/yyyy"), 

"Unknown"))) 

6.) Create a cross-joined table using the following DAX:

Table =  

CROSSJOIN ( DISTINCT ( 'Latest Date Table'[User - User Full Name]), DISTINCT ('Latest Date Table'[Training - Training Title])) 

7.) Added a new column in my cross-joined table to get all my statuses into one column and return my “not assigned” values:

    LOOKUPVALUE ( 

        'Latest Date Table'[Transcript - Transcript Status], 

        'Latest Date Table'[User - User Full Name], 'Table'[User - User Full Name], 

        'Latest Date Table'[Training - Training Title], 'Table'[Training - Training Title] 

    ) 

VAR vResult = 

    SWITCH ( 

        TRUE, 

        vStatus = "Completed", 

            CONVERT ( 

                LOOKUPVALUE ( 

                    'Latest Date Table'[Latest Completion Date], 

                    'Latest Date Table'[User - User Full Name], 'Table'[User - User Full Name], 

                    'Latest Date Table'[Training - Training Title], 'Table'[Training - Training Title] 

                ), 

                STRING 

            ), 

        vStatus <> "Completed" 

            && NOT ISBLANK ( vStatus ), vStatus, 

        "Not Assigned" 

    ) 

RETURN 

    vResult