Format a text value to look like a date value in DirectQuery

[Anonymous]

Hello everyone,

I have a DQ model, which means I can't use the FORMAT function. I needed to create a calculated column of type "Text" to store a bucket of date values, along with a text value of "Past Due", see below:

Week Due String = LEFT('Date'[DateKey] - WEEKDAY('Date'[DateKey], 1)+1, 11)  -- week due is defined as the Sunday of the current week.

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

Yields a value of either "Past Due", or the date output which looks like "Mar 12 2019" I guess it defaults to this format when it is of type Text. I'd like it to appear as the normal 3/12/2019, but it has to remain in text/string format so that I can keep the "Past Due" bucket an option for the matrix visualization and aggregation: Week Due Bucket is the Matrix Column value.

 

Any ideas would be great!

[v-juanli-msft · Accepted solution]

Hi @Anonymous

I can reproduce your problem.

Please create columns

Column = MONTH([Week Due])&"/"&DAY([Week Due])&"/"&YEAR([Week Due])

Week Due Bucket2 = IF([This Week] > [Week Due], "Past Due", [Column])

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Perfect, thanks Maggie!

[v-juanli-msft]

Hi @Anonymous

Which is the [this week] ?

I test as below, but i can't reproduce your problem.

Please share a example as above and how does the "this week" calculate?

Best Regards

Maggie

[Anonymous]

This Week return a single date value - the sunday of the current week.

This Week = TODAY() - WEEKDAY(TODAY(), 1)+1

Week Due = 'Date'[DateKey] - WEEKDAY('Date'[DateKey], 1)+1  --column data type Date

Week Due String = LEFT('Date'[DateKey] - WEEKDAY('Date'[DateKey], 1)+1, 11)  -- column data type of Text

Week Due Bucket = IF([This Week] > [Week Due], "Past Due", [Week Due String]) -- column data type of Text to allow for "Past Due" as a possible value.