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 

[dufoq3]

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]

[mohammedald]

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 

[dufoq3]

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),
    Ad_CloseYear = Table.AddColumn(Ad_EstCloseYear, "Close Year", each Date.Year([Close Date]), Int64.Type),
    Ad_NewCloseDate = Table.AddColumn(Ad_CloseYear, "New Close Date", each 
        [ 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
    Ad_NewCloseDate

[mohammedald]

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 

[dufoq3]

Heve you read note below my post?

[mohammedald]

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?

[mohammedald]

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?

[dufoq3]

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

[mohammedald]

Yes ChangeType erorr

[dufoq3]

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

[mohammedald]

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

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

    Ad_NewCloseDate = Table.AddColumn(Ad_CloseYear, "New Close Date", each

        [ 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

    Ad_NewCloseDate

[dufoq3]

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.

[mohammedald]

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" 

[dufoq3]

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.

[mohammedald]

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
Source = Excel.Workbook(File.Contents("C:\Users\Mohammed\Downloads\Test Date.xlsx"), null, true),
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),
Ad_CloseYear = Table.AddColumn(Ad_EstCloseYear, "Close Year", each Date.Year([Close Date]), Int64.Type),
Ad_NewCloseDate = Table.AddColumn(Ad_CloseYear, "New Close Date", each
[ 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
Ad_NewCloseDate

[dufoq3]

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.

[mohammedald]

Here is the sample data with the error 

https://drive.google.com/drive/folders/1tVJbW8RdRg7jgNqVb4BDKpnqQ9RdPAJv?usp=sharing

[dufoq3]

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
    Source = Excel.Workbook(File.Contents("C:\Users\Mohammed\Downloads\Test Date.xlsx"), null, true),
    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),
    Ad_CloseYear = Table.AddColumn(Ad_EstCloseYear, "Close Year", each Date.Year([Close Date]), Int64.Type),
    Ad_NewCloseDate = Table.AddColumn(Ad_CloseYear, "New Close Date", each 
        [ 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
    Ad_NewCloseDate

[mohammedald]

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

[dufoq3]

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