Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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')

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1656999288127.png


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.

amitchandak
Super User
Super User

@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
Not applicable

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')

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.