Forum Discussion
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
https://drive.google.com/file/d/151klFfZyEViNb4DEV079vB_UqAImkJjc/view?usp=sharing
Thank you
26 Replies
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:
- [Deal Stage] <> "Won"
- [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]
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
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
