Forum Discussion
Resolver IDate Table, Last X Completed Months
I am trying to add a Last X Completed months by Date Table, and unsure of the best/simpliest approach to do it.
I was able to quickly add a TRUE/FALSE for determining the Last X Months. But how would add a column to my date table with a TRUE/FALSE for the Last X Completed Months?
This is what I used for the Last X 2 Months.
Table.AddColumn(#"Added custom 4", "Last 2 Months", each Date.IsInPreviousNMonths(Date.AddMonths([Date], -1), 2))
I think i need to compare Todays date if <= current months end of month, and then back up a month? Or is there a simpler way?
pls try
[ X = 2, s = Date.StartOfMonth( Date.AddMonths( Date.From( DateTime.LocalNow()),-X)), e = Date.AddDays( Date.StartOfMonth( Date.From( DateTime.LocalNow())),-1), result = [date]>=s and [date]<=e] [ result]
3 Replies
- Ahmedx
Super User
pls try this
[ X = 3, d = Date.StartOfMonth( Date.AddMonths( Date.From( DateTime.LocalNow()),-X)), result = [date]>=d ][result] 
ptmuldoonThat's pretty close, but dont' think I'm quite 100% there yet with it. And I didn't know you could use variables like that in a Column Add, that's good to know.
I modified your code a little bit to include both Start and End date, and this currently works as of today. But I think it will give an incorrect result when your actually on the end of a month. So I currently have this below, which provided TRUE for the Sept/Oct dates. But when the actual end of month hits, I think it may still fall back to the prior 2 months?
[ X = 2, s = Date.StartOfMonth( Date.AddMonths( Date.From( DateTime.LocalNow()),-X)), e = Date.EndOfMonth( Date.AddMonths( Date.From( DateTime.LocalNow()),-1)), result = [Date]>=s and [Date]<=e] [ result]- Ahmedx
pls try
[ X = 2, s = Date.StartOfMonth( Date.AddMonths( Date.From( DateTime.LocalNow()),-X)), e = Date.AddDays( Date.StartOfMonth( Date.From( DateTime.LocalNow())),-1), result = [date]>=s and [date]<=e] [ result]