Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have shared a simple dummy chart. I want to convert the date with the below 03 conditions-
1. If the Date does not exist, then it will return "N/A"
2. If the Date is less than today's date, then it will return Date
3. If the date is higher than today's date, then it will return "Est. Date".
I using the below code, but this is not working.
if Value.Is(Number.From(DateTime.FromText([Date]) ), type number)
then
if DateTime.FromText([Date]) < DateTime.LocalNow() then
[Date]
else "Est. " & [Date]
else "N/A"
Solved! Go to Solution.
Magic 😄
@wdx223_Daniel I have facing a Date format issue. your formula is giving DateTime format whereas I am looking for Date format only as in below picture.
=if not([Date] is datetime) then "N/A" else (if [Date]< DateTime.LocalNow() then "" else "Est. ")&DateTime.ToText([Date],"dd/MM/yyyy")
@wdx223_Daniel your formula is working fine. can you please advise what was the mistake in my formula?
there are many data type in your data. DateTime.FromText is only used to convert Text to DateTime, So, your first two conditions will get errors when the value is a datetime already. And, Text and DateTime can not be concatenated directly, that will give a error too.
@wdx223_Daniel is working fine. you are great.
=if not([Date] is datetime) then "N/A" else if [Date]< DateTime.LocalNow() then [Date] else "Est. "&DateTime.ToText([Date])
@wdx223_Daniel Thanks for your quick response. But I am getting only "N/A". Please see the below snapshot. I do not change my data to Date format, Please see the below mark in the snapshot. I dont want to change the data format to DATE. I want to solve it with a formula. Please help.
not([Date] is datetime)
if not([Date] is date) then "N/A" else if [Date]< DateTime.LocalNow() then [Date] else "Est. "&DateTime.ToText([Date])
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.