how do I change date format in CONCATENATEX

[bsz412]

Hi, 

I would need to create a list on a card which shows the months I chose from a slicer, in this format: 

Example: 

Jan21, Feb21, Mar21, Apr21  

With the following code I could create something similar, but it gives me only month names, so not exactly what I need. 

CONCAT TEST =

CONCATENATEX(

VALUES(AW_Calendar_Lookup[Date].[Month]),

AW_Calendar_Lookup[Date].[Month],

","

)

Result: January, February, March, April

Can you please help me how to produce the required format?

thank you

[MarkLaf · Accepted solution]

Without knowing what your Dates table looks like, you can try something like:

Measure = 
VAR _MMMYYTable = 
    SUMMARIZE( 
        GENERATE( Dates, ROW( "label", FORMAT( Dates[Date], "MMMYY" ) ) ),
        [label] 
    )
VAR _concat = 
    CONCATENATEX( _MMMYYTable, [label], ", " )
RETURN
_concat

Note that if you add a column to your Dates table with the text you want, e.g. a [MMMYY] column, then _MMMYYTable = VALUES ( Dates[MMMYY] ) could be used instead of the SUMMARIZE/GENERATE

[bsz412]

Thanks so much, both versions you provided work perfectly! Awesome!

[amitchandak]

@bsz412 , Try like

CONCAT TEST =
CONCATENATEX(
VALUES(AW_Calendar_Lookup[Date].[Month]),
format(AW_Calendar_Lookup[Date], "MMMM"),
","
)

[bsz412]

@amitchandak 

it gives the following error message 

A single value for column 'Date' in table 'AW_Calendar_Lookup' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation su

[amitchandak]

@bsz412 , Sorry, Try this

CONCAT TEST =
CONCATENATEX(
VALUES(AW_Calendar_Lookup[Date].[Month]),
format(max(AW_Calendar_Lookup[Date]), "MMMM"),
","
)

[bsz412]

The problem with this one is that it repeats the MAX value like :

August, August, August, August