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 ...
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.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity 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.