Forum Discussion

mohammedald's avatar
mohammedald
Helper I
2 years ago

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...
Need Help
mohammedald's avatar
mohammedald
Helper I
2 years ago

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's avatar
dufoq3
Community Champion
2 years ago

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