Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

undefined

is there any custom column formula where date value in particular range can be appended as text value?Example if a date range is in between systemdate-1 as "yesterday" or sysdate+1 as "tommorw" ? could someone please let me know if there is any such case condition to write in DAX or custom column?

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thanks for replying, to be very clear so there is a column which is being fetched fromoracle database which contains date values in it as in format of 01-MAY-22 , so dax or custom column has to check each value in the column and has to create a new column where if its  today(01-JUL-22) as today similar way today-7 as "last week" etc attaching oracle query which has to be converted to dax or custom column 

    (ex : 

    WHEN TO_CHAR(H03_DATE_017,'YYYYMMDD') = TO_CHAR(SYSDATE-1,'YYYYMMDD') THEN 'Yesterday'

    WHEN TO_CHAR(H03_DATE_017,'YYYYMMDD') = TO_CHAR(SYSDATE,'YYYYMMDD') THEN 'Today')

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Not very clear, You can have column like

     

    Date Type =
    SWITCH(TRUE(),
    'Date'[Date] = TODAY(), "Today",
    'Date'[Date] = TODAY() -1 , "Yesterday",
    'Date'[Date] = TODAY() +1 , "Tomorrow",
    [Date]& "")

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for replying, to be very clear so there is a column which is being fetched fromoracle database which contains date values in it as in format of 01-MAY-22 , so dax or custom column has to check each value in the column and has to create a new column where if its  today(01-JUL-22) as today similar way today-7 as "last week" etc attaching oracle query which has to be converted to dax or custom column 

      (ex : 

      WHEN TO_CHAR(H03_DATE_017,'YYYYMMDD') = TO_CHAR(SYSDATE-1,'YYYYMMDD') THEN 'Yesterday'

      WHEN TO_CHAR(H03_DATE_017,'YYYYMMDD') = TO_CHAR(SYSDATE,'YYYYMMDD') THEN 'Today')

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Anonymous , you should change the data type to date and the code I have given should work

         

        Or you can create a new date column

         

        date 1=  datevalues([Date])

         

        If this does not help
        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Anonymous ,

    You could add a column by dax:

    Column = IF([Date]=TODAY()-1,"Yesterday",IF([Date]=TODAY()+1,"Tomorrow",IF([Date]=TODAY(),"Today",FORMAT([Date],"YYYYMMDD"))))

    The final show:


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