Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 mon...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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's avatar
    Anonymous
    4 years ago

    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's avatar
BA_Pete
Super User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      BA_Pete
      Super User
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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"