Create date format to show this month last month etc

[PaulTHR]

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 🙂

[PaulTHR]

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

[JamesRobson]

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")

[lbendlin]

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

[PaulTHR]

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

[PaulTHR · Accepted solution]

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"