Forum Discussion
Continued ContributorHow to show Previous month in date table
Hi folks,
I have a date table and within this I have a column that shows the current month so I can then create a Flag for current month.
Number.ToText(Date.Year(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10))) & "-" & Number.ToText(Date.Month(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)))
What I need to do is show the previous month as a Flag, as the business process is adding a months total during the first week of the next month. So when I display the data it comes up as May instead of April for date entered,
I will use this column in the date table to then show the outstanding data entities for April not May, as I want to add this column as a visual filter, so that it does not filter the rest of the page.
How can I achieve this is M?
I have had a look in the M dictionary (as I am new to M) but i cant quite get the right combination of what I want.
Any help gratefully received.
You can add a custom column like so:
= Table.AddColumn(PriorStepOrTableName, "Month Flag", each if Date.IsInCurrentMonth([Date]) then "Current Month" else if Date.IsInPreviousMonth([Date]) then "Previous Month" else "All Other Months", type text)
You can also use Date.IsInPreviousNMonths to take this as far back as you'd like.
--Nate
2 Replies
You can add a custom column like so:
= Table.AddColumn(PriorStepOrTableName, "Month Flag", each if Date.IsInCurrentMonth([Date]) then "Current Month" else if Date.IsInPreviousMonth([Date]) then "Previous Month" else "All Other Months", type text)
You can also use Date.IsInPreviousNMonths to take this as far back as you'd like.
--Nate
- DemoFour
Morning Anonymous
That's great, the mistake that I made was not to nest the DateIsInPreviousMonth in another IF statement, so that when I was using this function it put the Flag in the current month, I guess it makes sense that you can nest IF functions in M.
Thanks for your help 🙂
