Forum Discussion
I need Help With A Date Formula
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_NewCloseDateWow 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
Heve you read note below my post?