none
Converting Excel date formula into M RRS feed

  • Question

  • Good afternoon everyone,

    Due to the fact that I have avoided M code previously I am encountering a lot of issue now trying to perform my calculation at the power query level as oppose at the worksheet excel level. I have the following formula to calculate if a date field is equal to the first month of the last quarter and the excel formula is below, these calculation is for setting up a dataset for pivoting. However as an M n00b I am having trouble writing embedded date calculation in M and would be super appreciative of your expert assistance!!

    what the formula below does is to see if [@TrueFinalised] field is equal to the 1st day of the first month within the last quarter and if yes then return 1, if not then return 0. However all the formula really needs to do is whether the date is in the first, second or third month of previous quarter cycle.

    Excel formula:

    =IF(DATE(YEAR(TODAY()),FLOOR(MONTH(TODAY())-4,3)+1,1)=[@TrueFinalised],1,0)

    From my research so far I found M code to determine if a date is within the previous quarter using Date.IsInPreviousNQuarters but the complexity of M is totally confusing the heck out of me!

    Thank you very much in advance for any hints/assistance!

    Wednesday, March 6, 2019 7:02 AM

Answers

  • Hi Risemann,

    Greatly appreciate your input.. but I have solved this question within the last 2hrs of posting it!! which is pretty amazing for me given that I'm still pretty much a n00b. Below are my workout and I hope that this may help someone in the future with similar queries.

    Firstly I created a helper column to identify that the date field (TrueFinalised) is in the last quarter. Please note that the truefinalised date field have been transformed to the 1st of each month.

    = Table.AddColumn(#"Changed Type", "InLastQuarter", each if Date.IsInPreviousNQuarters([date],1) then 1 else 0)

    Then using this column I use 2 M function to calculate if they are in the first Month of last quarter, second or third.

    To find if the date belongs to the first Month of last quarter:

    if [InLastQuarter] = 1 and [date] = Date.StartOfQuarter([date]) then 1 else 0

    To find if the date belongs to the second month of last quarter:

    if [InLastQuarter] = 1 and [date] = Date.AddMonths(Date.StartOfQuarter([date]),1) then 1 else 0
    And finally if the date belongs to the third month of last quarter:
    if [InLastQuarter] = 1 and [date] = Date.AddMonths(Date.StartOfQuarter([date]),2) then 1 else 0

    And there we go! Problem solved!!!


    • Marked as answer by SeanYeoh Wednesday, March 6, 2019 9:12 AM
    Wednesday, March 6, 2019 9:12 AM

All replies

  • If you want to have a true/false answer, you can write this as a step:

    addColumn = Table.AddColumn(previousStep, "newColumnName",each Date.IsInPreviousQuarter([#"[@TrueFinalised]"]))

    This will create a new column after the step "previousStep" with a column name "newColumnName" based on the field [@TrueFinalised]. The extra letters around [@TrueFinalised] are escape characters.


    • Edited by S.Risemann Wednesday, March 6, 2019 8:31 AM
    Wednesday, March 6, 2019 8:31 AM
  • Hi Risemann,

    Greatly appreciate your input.. but I have solved this question within the last 2hrs of posting it!! which is pretty amazing for me given that I'm still pretty much a n00b. Below are my workout and I hope that this may help someone in the future with similar queries.

    Firstly I created a helper column to identify that the date field (TrueFinalised) is in the last quarter. Please note that the truefinalised date field have been transformed to the 1st of each month.

    = Table.AddColumn(#"Changed Type", "InLastQuarter", each if Date.IsInPreviousNQuarters([date],1) then 1 else 0)

    Then using this column I use 2 M function to calculate if they are in the first Month of last quarter, second or third.

    To find if the date belongs to the first Month of last quarter:

    if [InLastQuarter] = 1 and [date] = Date.StartOfQuarter([date]) then 1 else 0

    To find if the date belongs to the second month of last quarter:

    if [InLastQuarter] = 1 and [date] = Date.AddMonths(Date.StartOfQuarter([date]),1) then 1 else 0
    And finally if the date belongs to the third month of last quarter:
    if [InLastQuarter] = 1 and [date] = Date.AddMonths(Date.StartOfQuarter([date]),2) then 1 else 0

    And there we go! Problem solved!!!


    • Marked as answer by SeanYeoh Wednesday, March 6, 2019 9:12 AM
    Wednesday, March 6, 2019 9:12 AM