power query date evaluation error

rjmdc

Well-known Member
Joined
Apr 29, 2020
Messages
693
Office Version
  1. 365
Platform
  1. Windows
i cant proceed futher as my evaluation is not the expected result so far
i am expecting a 0 not a -1
-1 means not needed 0 means missing
i am getting a -1 as the result
brokeryear1 is 1/1/2021, brokeryear2 is 1/1/2022, adjusted broker approval is 1/1/2022
broker start date is 1/1/2022, year2dateend is 12/31/2022
it seems to be broke at the red statement.
what did i do wrong in the evaluation?

Rich (BB code):
= Table.AddColumn(#"Changed Type10", PTMYear2Count, each if Record.Field(_, BrokerYear1) = null then -1 neither are null so this isnt where the error is
else if  Record.Field(_, BrokerYear2) = null then -1 it is not null it says 1/1/2022
else if [Adjusted Broker Approval] < Record.Field(_, BrokerYear2)then -1 why is this failing 1/1/2022 is not less than 1/1/2022? it should have passed this step and moved to the next if
else if [PTM Date] = null and [Broker start date] >= Record.Field(_, BrokerYear2) and [Broker start date] <= Record.Field(_, Year2DateEnd)then 0
else if [PTM Date] <> null and  Record.Field(_, BrokerYear1) <> null and Record.Field(_, BrokerYear2) <> null and [PTM Date] >=Record.Field(_, BrokerYear1) and [PTM Date]<Record.Field(_, BrokerYear2) then 1
else 0)
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,223,099
Messages
6,170,111
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top