power query date evaluation error

rjmdc

Well-known Member
Joined
Apr 29, 2020
Messages
676
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,215,972
Messages
6,128,029
Members
449,414
Latest member
sameri

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