Forum Discussion

AndrejZitnay's avatar
AndrejZitnay
Post Patron
9 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(...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 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.

     

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
9 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
9 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 ()
)

 

 

 

  • AndrejZitnay's avatar
    AndrejZitnay
    Post Patron
    9 years ago

    Hello Eric_Zhang

     

    This is now sorted.

     

    thx

     

    Andrej