cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## I need Help With A Date Formula

Hello, Thank you so much for your time I will get right into the,

So to explain the problem so you have more context I have a dashboard that is connected through CRM online and we have deals that are closed as won in the system but the problem is sometimes it's not completely closed because some things are missing so the problem is some deals have a won status but doesn't have a close date value because the close date in this case is a system close date not an actual close date of the deal, so I used to update them manually in Excel but now after we moved into CRM online connection that isn't possible anymore so I need to write a Power Query formula to do it automatically until the issue is resolved, so I will show you a picture below that gives an example of how the data is like in the CRM and what is the correct output.

as you guys can see there are multiple conditions in this case for example the first one as you can see the [Est Close Date] is in 2023 but the [Close Date] is in 2024 and that is wrong because as I said the [Close Date] is a system close date not the actual deal close date, so as you can see in the [New Close Date] column that I added just to make the output clear, you can see the true value is 11/19/2023 = [Est Close Date] because the [Est Close Date] is in 2023 and the [Deal Stage] is "Won".

for the second one as you can see the [Close Date] is (Blank) but the [Deal Stage] is "Won" so I took the [Est Close Date] value and put it in the [New Close Date] because the [Est Close Date] is in 2023.

as for the third one as you can see the [Close Date] is (blank) but the [Deal Stage] is "Won" so I took the [Est Close Date] value and put it in the [New Close Date] because the [Est Close Date] is in 2024.

as for the fourth one and the fifth one it's all good because everything is in order.

I hope I made it clear how I want the formula to be and if you have any questions please let me know here is the link for the power bi file

Thank you

26 REPLIES 26
Super User

Hi, in your sample [New Close Date] = [Est Close Date] 🙂
You can just play with IF statement (but be careful, you have space at the end of "Est Close Date " column name)

You havent't described what do you want to return for cases when:

1. [Deal Stage] <> "Won"
2. [Close Date] < [Est Close Date) (Opp015)

In my opinion there could be also situations when [Close Date] year does not equal [Est Close Date] year but you want to return [Close Date]

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helper I

Hello dufoq3, thank you for your reply, but no [New Close Date] doesn't equal [Est Close Date] I want to make a new column in power query [New Close Date] with the conditions that are in the photo I uploaded so for example if the [Est Close Date] = 2023 and the [Close Date] = 2024 then [New Close Date] <(This is the new coulmn) = [Est Close Date]

another example if [Est Close Date] 2024 and [Close Date] is (Blank) then today's day

and lastly if [Est Close Date] is 2023 and [Close Date] = 2024 then [New Close Date] =  [Est Close Date]

regarding you qustion about "[Deal Stage] <> "Won"" in this case if the deal stage dosen't equal "Won" return null becasue the deal is not closed,

regarding the second qustion "[Close Date] < [Est Close Date) (Opp015)" yes I put it in there in purpose becasue somtimes the deal is closed before the Estimtaed Close Date ([Est Close Date]) in that case it should just return the  [Close Date],

I hope I made it clear please if you need anything else just let me know, and please excuse my limted knowledge in power query this is my first time using it

Super User

I didn't use hardcode values 2023 and 2024 because it won't work next year. Instead of that I used thisYear and compared vs thisYear.

Result

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZZLbtwwDIavMph1OhAp6rXvqkBQoF10EWQxfQANAjRBFzmPz+KTlZRtiYwrJ6sxDX2mxP8nNXd358/Pzx+cg/PN+eP15eEn/yLOU8DET+ly8pcTOvQcQODHGhFH357+nO9vNhz5zZen74KUefJZ1mO5nEKjw/apPewbjGmeIMjHeG18V2Z58/X69/pboDhPKZaViI3YQUFBhU+bIK9QHkOxbTPQPFHZEBgjSVWVYJ5yLktVczubCSwtCT5dfzyuJ/M+vi6MCSxcOhxYkljqesfbxY7b0HwAnMrOW09FLMKSprbeBBbWdoI8T1gLjF4TNrI8aoH8PLkopQReXrorTGT5biniyuVauaDNvCOoEcVxuaop4UhcCN0PckKqRNY2l2jgW4iqQpHr6+u2GKBxxtQyRm4UxPgmkfseRQQpK5BpLRpaCIoWMYmHcEnoDT7QEJ3uTE5Pzq2qhfekR+hjgTWELMm5muC6g0xk6T6RAud2tVubHP9zAHbPiJ6htqqdYTuEtIg8R3K1qRh77BvsvsnsbPQ1D2nf2MjSsbclME6Blk48SJiUEGI1qmY33bdjstbeS0Os7Y/jPNovhf3iy9pENMzj1ZRJaetUODyP18Ml8N5KdssgVWZIY2t4fOXrZSqT9rUJLO2tADnDOknVWHZDV/s+ZyLTgBsNMC5Sdwy4re+LtsiOUHcV906pFihHk8L32ULcLyXAqsNBkqx8hbyxXJvsWDt1LRW5RZeGQXN+CQdjk0Tp24fHX9I9nNFtl323sr2SLa2dk+VfTp2Ib4p3/w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Opp-Num" = _t, Name = _t, Value = _t, #"Est Close Date " = _t, #"Close Date" = _t, #"Deal Stage" = _t]),
TrimmedColumnNames = Table.TransformColumnNames(Source, Text.Trim),
ChangedType = Table.TransformColumnTypes(TrimmedColumnNames,{{"Value", type number}, {"Est Close Date", type date}, {"Close Date", type date}}),
Ad_EstCloseYear = Table.AddColumn(ChangedType, "Est Close Year", each Date.Year([Est Close Date]), Int64.Type),
[ today = Date.From(DateTime.FixedLocalNow()),
thisYear = Date.Year(today),
result = if [Deal Stage] <> "Won" then null else
if [Close Date] = null and [Est Close Year] = thisYear then today else
if [Close Date] = null and [Est Close Year] < thisYear then [Est Close Date] else
if [Close Year] > [Est Close Year] then [Est Close Date]
else [Close Date]
][result], type date)
in

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helper I

Wow this is a lot, thank you dufoq3, I am sorry but could you make it like this code below

= Table.AddColumn(#"Added Custom10", "Test Close Date formula", each if [Deal Stage] = "Won" then

if [Close Date] = null or [Close Date] = "" then

if [Est Close Date] = #date(2023) then [Est Close Date]

else if [Est Close Date] = #date(2024) then Date.From(DateTime.LocalNow())

else if [Est Close Date] = null or [Est Close Date] = "" then Date.From(DateTime.LocalNow())

else null

else [Close Date]

else null)

This was from me trying to do it with POE and of course it failed but I don't know how to apply your code

Super User

Heve you read note below my post?

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helper I

sorry I just saw it, but it seems that I can't get my query is this because I use Dynamics 365 online to connect the dataset?

Helper I

I think I figured it out, sorry this is my first time using Power Query so please be patient with me

when I changed the query as you said in the note the code worked but it gave me an error in the new column rows when I clicked the error it said this

Expression.Error: The column 'Value' of the table wasn't found.

Details:

Value

I think this is because the true value name in my data set is not the same the only problem is I have nearly 400 columns in the dataset do I need to identfy them all?

Super User

You have Value column in your sample data. Which step cause an error? ChangeType?

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helper I

Yes ChangeType erorr

Super User

You can delete this part {"Value", type number}, from ChangedType step.

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helper I

First of all, thank you dufoq3 for being patient with me, but now this error shows up, but I am 100% certain that I have that column name

Expression.Error: The column 'Est Close Date' of the table wasn't found.

Details:

Est Close Date

This is the whole code I enterd is it corect? or did I do somthing wrong

let

Source = opportunities_table,

TrimmedColumnNames = Table.TransformColumnNames(Source, Text.Trim),

ChangedType = Table.TransformColumnTypes(TrimmedColumnNames,{{"Est Close Date", type date}, {"Close Date", type date}}),

Ad_EstCloseYear = Table.AddColumn(ChangedType, "Est Close Year", each Date.Year([Est Close Date]), Int64.Type),

[ today = Date.From(DateTime.FixedLocalNow()),

thisYear = Date.Year(today),

result = if [Deal Stage] <> "Won" then null else

if [Close Date] = null and [Est Close Year] = thisYear then today else

if [Close Date] = null and [Est Close Year] < thisYear then [Est Close Date] else

if [Close Year] > [Est Close Year] then [Est Close Date]

else [Close Date]

][result], type date)

in

Super User

Hi, double check that column name: double click to column header and check if the name is exactly: "Est Close Date" - if not, rename it. You have to perform rename before ChangedType step - so select Source step and then double click on the column name you want to rename.

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helper I

Hello, I tied using the original name of the columns and it did solve the error the [Est Close Date] was [estimatedclosedate] and the [Close Date] was [actualclosedate] when I changed the names in the code it works with no error but there is one problem the values of the new column are all "Table" I thoght it might be okay but when I tested it in the dashboard all the values of the new column are "Table"

Super User

I can only work with sample data you provided (which does not match with reality). It would be good to share few rows of real data (i.e. via google drive with link public permissions). Remove sensitive data if necessary.

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helper I

I tried the code with the sample data and it also gave me an

Expression.Error: The column 'Value' of the table wasn't found.
Details:
Value

and as you know the "value" column is there and then I deleted the value as you said before and still the same erorr but with "Est Close Date" and this is using the sample data I provided this is my code

let
TrimmedColumnNames = Table.TransformColumnNames(Source, Text.Trim),
ChangedType = Table.TransformColumnTypes(TrimmedColumnNames,{
{"Est Close Date", type date}, {"Close Date", type date}}),
Ad_EstCloseYear = Table.AddColumn(ChangedType, "Est Close Year", each Date.Year([Est Close Date]), Int64.Type),
[ today = Date.From(DateTime.FixedLocalNow()),
thisYear = Date.Year(today),
result = if [Deal Stage] <> "Won" then null else
if [Close Date] = null and [Est Close Year] = thisYear then today else
if [Close Date] = null and [Est Close Year] < thisYear then [Est Close Date] else
if [Close Year] > [Est Close Year] then [Est Close Date]
else [Close Date]
][result], type date)
in

Super User

I'm sorry, but my query definitely works with sample data. I see space in your sample screenshot in Value column. There is probably also space at the end of column name Est Close Date. But I handled extra spaces at the beginning and end of column name with TrimmedColumnNames step - so this shouldn't be an issue.

Sharing your query won't help me. I don't see your data / column names etc. so I can't help you without this.

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helper I
Super User

I need this file...
C:\Users\Mohammed\Downloads\Test Date.xlsx, but what I see from your query is that you didn't read carefully my Note!

Try this, but this time do not add it as custom column. Open Advanced Editor, delete whole query and paste there this one.

``````let
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
TrimmedColumnNames = Table.TransformColumnNames(Table1_Table, Text.Trim),
ChangedType = Table.TransformColumnTypes(TrimmedColumnNames,{
{"Est Close Date", type date}, {"Close Date", type date}}),
Ad_EstCloseYear = Table.AddColumn(ChangedType, "Est Close Year", each Date.Year([Est Close Date]), Int64.Type),
[ today = Date.From(DateTime.FixedLocalNow()),
thisYear = Date.Year(today),
result = if [Deal Stage] <> "Won" then null else
if [Close Date] = null and [Est Close Year] = thisYear then today else
if [Close Date] = null and [Est Close Year] < thisYear then [Est Close Date] else
if [Close Year] > [Est Close Year] then [Est Close Date]
else [Close Date]
][result], type date)
in

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helper I

I uploaded the Excel file but I have a question my real data is connected through Daynimc 365 online dose that make a difference?

Super User

I've edited my previous post few seconds ago - read it please.

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors