Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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!
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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.
       
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Perfect, thanks Maggie!