power query date evaluation error

rjmdc

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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,225,353
Messages
6,184,457
Members
453,233
Latest member
bgmb

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