Date bucket with text value and sorting column in Matrix

[Anonymous]

Hi all,

I've been trying to implement a solution to no avail. Your brilliant suggestions are appreciated.

I am using the latest PBI Desktop in direct query mode. I have a column called "Week Due Bucket" where all dates before a certain day are lumped into a field called "Past Due" and any dates in the future use their literal date, based on the Sunday of that week. I had to create a text column in order to store "past due" in the same column as the dates. I can't use FORMAT with DQ, so I had to do:

Week Due String = LEFT('Date'[DateKey] - WEEKDAY('Date'[DateKey], 1)+1, 11) (This just pulls the Sunday for the week in which it was due, and the date looks like Jan 21 2019 as pictured.

Week Due Bucket = IF([This Week] > [Week Due], "Past Due", [Week Due String])

Week Due Order = IF('Date'[Week Due Bucket] = "Past Due", 0, WEEKNUM('Date'[Week Due Bucket], 1)

I'm displaying in a Matrix in two ways as seen in attached photos. My issue that 1) I'd like the date to look like 1/1/2019 and still maintain the "Past Due" text value bucket, and 2) I'd like to be able to sort these chronologically. I tried to sort week due bucket by week due order, and got this error:

Matrix visual preview, Week Due Bucket as column, unsortable:

 

 

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

As you are using Direct query, So you should edit your data in the data source to change the data type. Here we cannot change the sort in Power BI side directly. Or we can import the data and edit the data model in PQ to achieve our goal.

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank