Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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 ()
)
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |