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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors