Power Query formula

[Anonymous]

Hello  -  We have business logic that says:  Based on the forecsat lockdown month  (we "lock" our CRM forecast down at the 1st of each month and give it a file name like October 2021), then add 2 months to the Customer Requested Ship Date.    We then compare what was ordered in that month (the month that is 2 months out from the lockdown file), to see how we did on our forecast.  

Essentially, Lockdown Month column +  2 months is what the Customer requested ship date column needs to be filtered to.   I could add a conditional column of course, but is there a way to apply this logic via m code instead of having to write multiple conditional statments?

So, in my table, I have a forecast month column, and a requested ship date column.   And the logic would be: 

Lockdown Month                          Customer Requested Ship Date

August 2021                                  Any dates between Oct 1 & Oct 31

September 2021                            Any dates between Nov 1 & Nov 30

October 2021                                 Any dates between Dec 1 & Dec 31

[BA_Pete · Accepted solution]

Ah, ok.

Try something like this:

if
[reqShipDate] >= Date.StartOfMonth(Date.AddMonths([lockDownMonth], 2))
and
[reqShipDate] <= Date.EndOfMonth(Date.AddMonths([lockDownMonth], 2))
then "True"
else "False"

Pete

[BA_Pete]

Hi @Anonymous ,

I'm not sure I fully understand your requirement.

My understanding so far is this:

Given a lockdown month, you want a calculation to generate all the dates in the month that is two months after the lockdown month?

I think I'm confused by your use of "filtered" in the phrase "Lockdown Month column +  2 months is what the Customer requested ship date column needs to be filtered to".

Pete

[Anonymous]

Hi @BA_Pete    Yes, maybe wrong use of the term filtered.    Essentially, I probably need another column, and this column should be the ouput of a forumula that essentially says:

Maybe another way to tackle it is this: 

If the Requested Shipdate is two months later than the lockdown date, then True...if not then False.   

So I would need an added column with a formula that achieves the above.   I could then filter out all of the Falses on the report side of things.    This way, I would only be looking at a table of opportunties that fit the criteria above.   For example, opportunities that were in the lockdown file as of August, but had a requested ship date in October.  

[BA_Pete · Accepted solution]

Ah, ok.

Try something like this:

if
[reqShipDate] >= Date.StartOfMonth(Date.AddMonths([lockDownMonth], 2))
and
[reqShipDate] <= Date.EndOfMonth(Date.AddMonths([lockDownMonth], 2))
then "True"
else "False"

Pete

[Anonymous · Accepted solution]

@BA_Pete   Thanks for your help.  With some additonal help in the forums I eventually figured out the correct formula...but you got me a starting point!   

if
[Customer Requested Ship Date] >= Date.StartOfMonth(Date.AddMonths([Locked File Month], 2))
and
[Customer Requested Ship Date] <= Date.EndOfMonth(Date.AddMonths([Locked File Month], 2))
then "True"
else "False"

[BA_Pete]

Hi @Anonymous ,

I'm obviously in a different timezone as your questions above all came through overnight.

Your final code is correct. I wrote the calculation freehand and forgot to close off the Date.StartOfMonth functions on both the condition lines, but you've got it sorted.

Sorry I wasn't available to help you at the time.

I'm going to update my post with the correct parentheses so it will hopefully help anyone else who has the same requirement.

Pete

[Anonymous]

@BA_Pete   I was able to get rid of the error in the code, by adding partenthesis.   However, now the column itself (the output of the formula) shows error.   So, perhaps the formula needs to be adjusted?

if
([Customer Requested Ship Date]) >= Date.StartOfMonth(Date.AddMonths([Locked File Month]), 2)
and
([Customer Requested Ship Date]) <= Date.EndOfMonth(Date.AddMonths([Locked File Month]), 2)
then "True"
else "False"

[Anonymous]

@BA_Pete    I've added in my column names.   Getting an error on the word "then" and it says token comma expected  

if
[Customer Requested Ship Date] >= Date.StartOfMonth(Date.AddMonths([Locked File Month], 2)
and
[Customer Requested Ship Date] <= Date.EndOfMonth(Date.AddMonths([Locked File Month], 2)
then "True"
else "False"

[Anonymous]

@BA_Pete   Hi Pete  -  Any idea what could be causing that error?   I've tried adding commas in various places to no avail.