Forum Discussion

AndrejZitnay's avatar
AndrejZitnay
Post Patron
8 years ago
Solved

Problem with sorting 19 months chart

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

 

 

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago

    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.

     

3 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago

    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's avatar
      AndrejZitnay
      Post Patron
      8 years ago

      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 ()
      )