To find the expiry date from a string in Power Bi

[jesly_ajin]

Hi All,

I have a power bi column as below in which the Certifications column gives me the Sr No; Certification Name; Start Date; End Date separated by semicolon. But every new certificate of the employee is separated by |.

SO, when I select a date on the slicer, I need to show those certificates that expired before that date.

Please help.

[v-rzhou-msft · Accepted solution]

Hi @jesly_ajin ,

According to your screenshot, I can see that there will be multiple certifications in each title. Here I suggest you to try Split function in Power Query Editor to transform [Certifications] in your table.

>Split [Certifications] by " | " by row > Split [Certifications] by ";" by columns. > Rename the column names.

Then the calculation will be easier.  You need to create an unrelated Dimdate table.

Calendar = CALENDARAUTO() 

Measure:

Measure = 
IF(MAX('Table'[End Date])<MAX('Calendar'[Date]),1,0)

Add this measure into visual level filter and set it to show items when value = 1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.