I need Help With A Date Formula

[mohammedald]

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

https://drive.google.com/file/d/151klFfZyEViNb4DEV079vB_UqAImkJjc/view?usp=sharing

Thank you 

[mohammedald]

I don't know much about coding in power query but couldn't we just write a code that fits in a custom column without all that source stuff?

[mohammedald]

Ok now it works in the sample but the main data still have issues I don't know if it's because the connection is online and I don't have the data in a local folder, I will start a fresh connection and try it again 

[dufoq3]

You have to prepare your real data to same state as you have you sample in excel. Then just edit Source step of my query and refer your query name with real data (you can check it again in note below, but read carefully)

[mohammedald]

Hello dufoq3, sorry it took me this long to get back to you yesterday I finally got the code working and sorted the problems I had with the source as you can tell I was new to Power Query and didn't know anything about it or how does it work, so the code works perfectly now and I'm sorry but I need one more thing, as you know this code only works with "Won" deals but could you make it that it would work for "Lost" also? the code is below and before you go to it I just wanted to let you know that I changed the "Deal Stage" in the code to the orginal value so in this case the "Deal Stage" is "alfa_roadmapstatus" I thgout it would be beter if I coded from the source not from the custom columns so in this case "Won" = 6 and "Lost" = 8 here is the code 

Source = OData.Feed(My Data Source, null, [Implementation="2.0"]),
opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data],
TrimmedColumnNames = Table.TransformColumnNames(opportunities_table, Text.Trim),
ChangedType = Table.TransformColumnTypes(TrimmedColumnNames, {
{"estimatedclosedate", type date}, {"actualclosedate", type date}
}),
Ad_EstCloseYear = Table.AddColumn(ChangedType, "Est Close Year", each Date.Year([estimatedclosedate]), Int64.Type),
Ad_CloseYear = Table.AddColumn(Ad_EstCloseYear, "Close Year", each Date.Year([actualclosedate]), Int64.Type),
Ad_NewCloseDate = Table.AddColumn(Ad_CloseYear, "New Close Date", each
let
today = Date.From(DateTime.FixedLocalNow())
in
if [alfa_roadmapstatus] <> 6 then null else
if [actualclosedate] = null and [estimatedclosedate] <> null and [estimatedclosedate] > today then today else
if [actualclosedate] = null and [estimatedclosedate] <> null then [estimatedclosedate] else
if [actualclosedate] <> null and [Close Year] > [Est Close Year] then [estimatedclosedate] else
[actualclosedate]
, type date),

[mohammedald]

Ok I started a new connection and I loaded it through Dynamic 365 Online and then I clicked transform data and went straight to Advanced Editor and this showed up 

let

    Source = OData.Feed(There is a link here to the CRM Conection, null, [Implementation="2.0"]),

    opportunities_table = Source{[Name="opportunities",Signature="table"]}[Data]

in

    opportunities_table

where can I find my source is it all of it ?

[mohammedald]

Imean here

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