How to add custom text in dropdown menu (for latest date)

[JustinDoh1]

I am trying to add a text like "Current = " for the latest date.

Bottom is illustration:

This is what I have, but I would like to modify so that it shows up as 

"Current = May 2024"  instead of "May 2024"

Basically, the data gets populated from SQL like this:

Do I have to create an extra column in SQL with a logic to add "Current =" (for latest date), and somehow add with the Processind Date data? 

But, how to concatenate with text data (Current = ) with Date/time data?

Currently, ProcessingDate column is "Date/time" data type.

I guess, I might be create a SQL View to concatenate two columns, and convert both columns into a text column possilby.

But, I would like to get some feedback if there is a way to do it in PBI. 

[parry2k · Accepted solution]

@JustinDoh1 Well you can add a duplicate column in Power BI but if you can add it at the backend, it is better. 

[Anonymous]

Hi,@JustinDoh1 .
Has your problem been solved? If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[Anonymous · Accepted solution]

Hi,@JustinDoh1 .I am glad to help you.
Hello,@parry2k .thanks for your concern about this issue.

Your answer is excellent! And I would like to share some additional solutions below.

According to your description, you want to modify the date column in the table: when the date value is current month, display it as "current=today()", change the date type to text type and add it in the slicer options.

If my understanding is correct

You can refer to my test result below

You can do this by creating a new calculated column in Desktop.
Here is my test data:

I created a new column C_result to format the date column data

C_result = 
IF(MONTH('Table_'[Processing Date])=MONTH(TODAY())&&YEAR('Table_'[Processing Date])=YEAR(TODAY()),
"Current="&[Processing Date],
""&[Processing Date]
)

This allows you to filter the data directly through the newly created C_result column as a slicer.

sugestion2: But this filtering is very limited because the data type is text.

So I suggest you can modify the filtering range by creating a measure that tags the data.

like this

I created a new date table as a formatted slicer option.

Table_slicer = VALUES('Table_'[C_result])

Create M_result to sift through the data

M_result = 
VAR selectedValue_=MAX('Table_slicer'[C_result])
VAR datevalue_=
IF(CONTAINSSTRING(selectedValue_,"Current"),
    DATEVALUE(RIGHT(selectedValue_,8)),
    DATEVALUE(selectedValue_)
)
RETURN
IF(MAX('Table_'[Processing Date])<=datevalue_,
1,0)

Add M_result=1 as a filter condition to visual
Implemented slicer to return data for a month and previous months when selecting a particular month's date

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[parry2k · Accepted solution]

@JustinDoh1 Well you can add a duplicate column in Power BI but if you can add it at the backend, it is better. 

[parry2k]

@JustinDoh1 yes, better to add another column in the SQL and have this column sorted by the original column so that it shows up in the correct order otherwise it will be alphabetically sorted because it will become a text column.

[JustinDoh1]

@parry2k So, there is no solution in PBI file itself to add something like this, right?