Sort Date Column in Matrix

[srlabhe123]

Hi All,

I have a scenario where I am using a date field as Column in matrix visusal. But when I am trying sorting on the same field its not working the way it shoud be. The older date reflecting as first column and newer date as last. I wanted to have exactly in reverse order

Below are the options I have alreday tried

1. Sorted on base of index of date,i.e. I alreday have a column which is index for date nothing but sequence of date in year.01/01 would have 1 and 02/01 would have 32 and so on.

2. In data model tried selecting date and sort by on index column

But its not working the way it should.

Help is appreciated here.

Thanks

[v-yuezhe-msft]

@srlabhe123,

In your scenario, please add the index in reverse order, in other words,  02/01 would have 1, 01/01 would have 32, then sort the date by index column. There is an example for your reference.



Regards,
Lydia

[srlabhe123]

Thanks, what wold be the formula for index here?

[v-yuezhe-msft]

@srlabhe123,

Could you please share sample data of your original table so that I can provide you appropriate DAX?

Regards,
lydia

[srlabhe123]

Please find sample data below:

COUNT_DATE	TaskCrenTime	TaskComplStamp	DIFF	WEEK_END_DATE	CALENDAR_DATE	MONTH	SYSDATE	MONTH_NAME	LAST_7_DAYS	DAY_NUMBER_OF_YEAR	ZONE	LOCATION	LOCN_ID	STAT_CODE	TASK_ID	EXCEPTION	ABC_INDICATOR	Expected Quantity	Counted Quantity	Qty Varaince	LatestComplnTime	EarliestCreanTime	TasksGenerated	TasksComplete	TaskExpired	% Complete	% Expired	Accuracy	AccuracyBinary
01/21/2019	01/21/2019	01/21/2019	380.92	27-JAN-19	21-JAN-19	1	22-JAN-19	JANUARY	Y	21	A2	A2-0925-02E	0032317	90	13272766	Pending	D	31	31	0	21-JAN-19 11.03.14.547000000 PM	21-JAN-19 04.42.19.363000000 PM	1	1	0	1	0	1	1
01/22/2019	01/22/2019	01/22/2019	0	27-JAN-19	22-JAN-19	1	22-JAN-19	JANUARY	N	22	C2	C2-0317-02F	0020160	90	13339038	Pending	D	44	44	0	22-JAN-19 03.53.33.083000000 PM	22-JAN-19 03.53.33.083000000 PM	1	1	0	1	0	1	1
01/22/2019	01/22/2019	01/22/2019	0	27-JAN-19	22-JAN-19	1	22-JAN-19	JANUARY	N	22	A3	A3-0927-01E	0030878	90	13339447	Pending	D	253	242	11	22-JAN-19 04.20.16.643000000 PM	22-JAN-19 04.20.16.643000000 PM	1	1	0	1	0	1.05	0
01/21/2019	01/21/2019	01/21/2019	341.22	27-JAN-19	21-JAN-19	1	22-JAN-19	JANUARY	Y	21	A1	A1-0416-03F	0032840	90	13272734	Pending	D	55	54	1	21-JAN-19 10.16.42.353000000 PM	21-JAN-19 04.35.29.230000000 PM	1	1	0	1	0	1.02	0

[v-yuezhe-msft · Accepted solution]

@srlabhe123,

What fields of the above table do you use to create Matrix visual? You can rank the date in Power BI Desktop query Editor following the guide in this similar blog.

Regards,
Lydia

[srlabhe123]

Thanks the blog helped me, it worked like charm... 

[srlabhe123]

Created matrix by Location as row, Calendar Date as Column and %complete as values in matrix