Problem with sorting 19 months chart

[AndrejZitnay]

Hello all,

I have 3 data sets.

Renewals, & Volumes.

I need to have chart with 19 months hence I have below new columns for visual.

MonthYear = FORMAT(Renewals[Date],"mmm")&"-"&FORMAT(Renewals[Date],"YY")

YearMonthNumber = (YEAR('Renewals'[Date]))*12+MONTH('Renewals'[Date])

Year Month Number is for sorting. This sort is good for data only from one data set.

Problem is when i have to pull data from two of my data sets. There isn't direct relationship hence I have Data table and link all of them.

Date Table 24 months =
FILTER (
    DISTINCT (
        UNION ( VALUES ( Renewals[MonthYear] ), VALUES ( Terminations[MonthYear Retention] ),VALUES(Volumes[MonthYearVolumes] ))
    ),
    ISBLANK ( [MonthYear] ) = FALSE ()

)

Problem is that sorting is always alphabetical.

Can anybody help?

Thanks

Andrej

[Eric_Zhang · Accepted solution]

@AndrejZitnay

You can create MonthYear as date instead

MonthYear = DATE(YEAR('Renewals'[Date]),MONTH('Renewals'[Date]),1)

And then in the table 'Date Table 24 months', create a calculated colume formated as MonthYear was and sort with the new MonthYear column.

[AndrejZitnay]

Hello @Eric_Zhang

Thank you for helping me out.

I've created 3 new columns in my data.

MonthYearVolumeNew = DATE(YEAR('Volumes'[FUNDED_DATE]),MONTH('Volumes'[FUNDED_DATE]),1)

MonthYearNew = DATE(YEAR('Renewals'[Date]),MONTH('Renewals'[Date]),1)

MonthYearRetetntionNew = DATE(YEAR('Terminations'[COMPLETIONDATE]),MONTH('Terminations'[COMPLETIONDATE]),1)

How I include them now to my Data Table 24 months?

This is my current layout. Are you suggesting to replace with new columns or just add them there for sort?

Date Table 24 months =
FILTER (
    DISTINCT (
        UNION ( VALUES ( Renewals[MonthYear] ), VALUES ( Terminations[MonthYear Retention] ),VALUES(Volumes[MonthYearVolumes] ),VALUES(Terminations[MonthYear Retention breakdown]))
    ),
    ISBLANK ( [MonthYear] ) = FALSE ()
)

[AndrejZitnay]

Hello @Eric_Zhang

This is now sorted.

thx

Andrej