sorting dates that have been concatenated via new quick measure tool

[Anonymous]

hi,

i created a measure to list dates using the new quick measure tool & the resultant dax is as flws:

YYYY+MMM.List =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Calendar'[YYYY+MMM])

VAR __MAX_VALUES_TO_SHOW = 3

RETURN

    IF(

        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,

        CONCATENATE(

            CONCATENATEX(

                TOPN(

                    __MAX_VALUES_TO_SHOW,

                    VALUES('Calendar'[YYYY+MMM]),

                    'Calendar'[YYYY+MMM],

                    ASC

                ),

                'Calendar'[YYYY+MMM],

                ", ",

                'Calendar'[YYYY+MMM],

                ASC

            ),

            ", etc."

        ),

        CONCATENATEX(

            VALUES('Calendar'[YYYY+MMM]),

            'Calendar'[YYYY+MMM],

            ", ",

            'Calendar'[YYYY+MMM],

            ASC

        )

    )

its purpose is to include the dates list in my visual title/captioning. however, the dates r listed alphabatically (eg. 2018-oct followed by 2018-sep) instead of chronologically (i.e. 2018-sep followed by 2018-oct). kindly  advise on how to resolve this in the dax above (that's generated via the new quick measure tool).

tks. -nik

[v-xicai]

Hi @Anonymous ,

You may create [YearMonth] column firstly, then select on the 'Calendar'[YYYY+MMM], go to Column tools ->Sort by column, choose [YearMonth].

YearMonth = VALUE(FORMAT('Calendar'[Date],"yyyymm"))

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous]

tks @v-xicai,

the solution seems to work for YYYY-MM column (createdd by YYYY+MM = FORMAT('Calendar'[Date], "yyyy-mm"). 

however, if i select the YYYY+MMM column (created by YYYY+MMM = 'Calendar'[YYYY]&"-"&'Calendar'[MMM]), i stil down get the column sorted chronologically.

i believe i'm still missing something.

appreciate any help.

tks, -nik

[v-xicai]

Hi @Anonymous ,

The data type of the two formulas below as you said is text . So I recommend you try the my formula " YearMonth = VALUE(FORMAT('Calendar'[Date],"yyyymm")) ", which is using VALUE function to convert text type to number type . Then follow the steps above.

This is Text type:

YYYY+MM = FORMAT('Calendar'[Date], "yyyy-mm" )

YYYY+MMM = 'Calendar'[YYYY]&"-"&'Calendar'[MMM])

Best Regards,

Amy 

Community Support Team _ Amy

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

[parry2k]

@Anonymous did you use sort by as recommended by @v-xicai. Here is a post I wrote on calendar table and that use sort by method. I hope this helps.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format?

[Anonymous]

hi @amitchandak,

as the file contains sensitive data from my client, i'm unable to share the pbix. additionally, its datasource is a dataset in pbi service.

many tks still.

krgds, -nik