Forum Discussion
Power Query formula
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 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"
Super UserHi 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
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
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 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"BA_Pete Hi Pete - Any idea what could be causing that error? I've tried adding commas in various places to no avail.
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"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