Forum Discussion

bsz412's avatar
bsz412
Icon for Helper III rankHelper III
4 years ago
Solved

how do I change date format in CONCATENATEX

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
 
Need Help
  • MarkLaf's avatar
    MarkLaf
    4 years ago

    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

6 Replies

  • MarkLaf's avatar
    MarkLaf
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      bsz412
      Icon for Helper III rankHelper III
      4 years ago

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

       

       

       

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    bsz412 , Try like

     

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

    • bsz412's avatar
      bsz412
      Icon for Helper III rankHelper III
      4 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        bsz412 , Sorry, Try this

         

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