Forum Discussion

PaulTHR's avatar
PaulTHR
Frequent Visitor
3 years ago
Solved

Create date format to show this month last month etc

Hi - is it possible in Powerquery to create a column alongside a date column that does the following...

Today is 17th May - make record state "This Month"
Previous date say 15th April would need to state "Last Month"
Month before that say 15th March would then say "Last Month +1"

The file is constantly being updated and I need to create measures using "This Month" etc..

I am only going to do this for 6 monthly points anything before will get ingored so easy enought to code it in I guess...

Thanks 🙂


Show and Tell
  • PaulTHR's avatar
    PaulTHR
    3 years ago

    I have managed to create a flag for "This Month" but need to add a "Last Month" & "Previous Month" & "Previous Month +!" etc etc - just 6 times anything out of date range I can 'Else' out

    if Date.StartOfMonth([date]) = Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())) then "This Month" else "Not this month"

5 Replies

  • PaulTHR's avatar
    PaulTHR
    Frequent Visitor
    3 years ago

    Thats did it - thanks very much for your suggestion it worked. Thanks

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    Yes, that is possible - as long as you refresh this in import mode frequently (daily)

    • PaulTHR's avatar
      PaulTHR
      Frequent Visitor
      3 years ago

      HI yes it reloads a number of times per day and every day so should be able to use Today()

      • PaulTHR's avatar
        PaulTHR
        Frequent Visitor
        3 years ago

        I have managed to create a flag for "This Month" but need to add a "Last Month" & "Previous Month" & "Previous Month +!" etc etc - just 6 times anything out of date range I can 'Else' out

        if Date.StartOfMonth([date]) = Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())) then "This Month" else "Not this month"

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Try this, you can jsut repeat the last else if until you get the desiered number of months (I only went to Last Month +1)

     

    = Table.AddColumn(#"Changed Type", "MonthCheck", each
    if Date.StartOfMonth([Date]) = Date.StartOfMonth(DateTime.Date(DateTime.FixedLocalNow()))
    then "This Month"
    else if Date.StartOfMonth([Date]) = Date.StartOfMonth(Date.AddMonths(DateTime.Date(DateTime.FixedLocalNow()),1))
    then "Next Month"
    else if Date.StartOfMonth([Date]) = Date.StartOfMonth(Date.AddMonths(DateTime.Date(DateTime.FixedLocalNow()),-1))
    then "Last Month"
    else if Date.StartOfMonth([Date]) = Date.StartOfMonth(Date.AddMonths(DateTime.Date(DateTime.FixedLocalNow()),-2))
    then "Last Month + 1"
    else "Check")