Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AndrejZitnay
Post Patron
Post Patron

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

)

 

sort.JPG

 

Problem is that sorting is always alphabetical.

 

Can anybody help?

 

Thanks

 

Andrej

 

 

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee

@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.

 

View solution in original post

3 REPLIES 3
Eric_Zhang
Employee
Employee

@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.

 

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

 

 

Capture.JPG

 

Hello @Eric_Zhang

 

This is now sorted.

 

thx

 

Andrej

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.