DAX not returning the correct Format

[Kay_Kalu]

I have the measure for my title 

TitleOfSelectedTerm2 = CONCATENATEX(VALUES(REF070_PRODUCT_TAXONOMY[COMPANY]),REF070_PRODUCT_TAXONOMY[COMPANY],", ",[COMPANY], ASC)&" Terminations & Lapses -
"&FORMAT(CONCATENATEX(VALUES('LIGHTHOUSE_CALENDAR'[SelectionDate]),LIGHTHOUSE_CALENDAR[SelectionDate],", ",[SelectionDate], ASC), "MMM YYYY")

Every other thing return propely except the format for the date. it returns 10/1/2024,11/1/2024 while I want Oct 2024, Nov2024.

[DataNinja777 · Accepted solution]

Hi @Kay_Kalu ,

The issue is that the FORMAT() function is being applied after CONCATENATEX(), so it’s trying to format a comma-separated string of dates rather than formatting each individual date before concatenation.

To fix this, you should format the individual dates inside the CONCATENATEX(), like this:

TitleOfSelectedTerm2 =
CONCATENATEX(
    VALUES(REF070_PRODUCT_TAXONOMY[COMPANY]),
    REF070_PRODUCT_TAXONOMY[COMPANY],
    ", ",
    [COMPANY],
    ASC
)
& " Terminations & Lapses - "
& CONCATENATEX(
    VALUES('LIGHTHOUSE_CALENDAR'[SelectionDate]),
    FORMAT('LIGHTHOUSE_CALENDAR'[SelectionDate], "MMM YYYY"),
    ", ",
    'LIGHTHOUSE_CALENDAR'[SelectionDate],
    ASC
)

This way, each SelectionDate is individually formatted to "MMM YYYY" before being concatenated. Let me know if you want to remove duplicates or fine-tune spacing.

Best regards,

[Kay_Kalu]

Thanks @DataNinja777 .