Forum Discussion
Power Query formula
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
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"
8 Replies
- BA_Pete
Super User
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
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