Power Query: Currently Dating?
18 July 2018
Welcome to our Power Query blog. This week, I look at some useful logical M functions to determine whether a date is in a particular period.
I will take a look at some Boolean Date() functions in M that are true if a date is in a particular period, for example in the current month. There are a lot of Boolean date functions available, so I will give an example for those involving months, and a couple of year functions. I will then summarise the remaining functions as they are similar to the month functions, but for different periods of time. These functions are usually used within other calculations, but it is helpful to understand what is available.
Date.IsInCurrentMonth
Date.IsInCurrentMonth(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the current month, as determined by the current date and time on the system.
I will look at Current Expenses Paid in the next screen.
I add a custom column to my table to show if the current expenses are paid this month.
I used the following M formula:
= Date. IsInCurrentMonth([Current Expenses Paid])
I can see that Mary does not receive her current expenses this month.
Date.IsInNextMonth
Date.IsInNextMonth(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the next month, as determined by the date and time on the system.
I will look if Next Expenses Paid occurs in the next month.
The M formula I have used is
= Date.IsInNextMonth([Next Expenses Paid])
I can see that Mary doesn’t get her next expenses next month.
Date.IsInNextNMonths
Date.IsInNextNMonths(datetime as any, months as number) as nullable logical
Indicates whether the given datetime occurs during the next number (months) of months, as determined by the current date and time on the system.
I will look at whether Christmas Bonus Paid takes place in the next six (6) months.
The M formula I have used is
= Date.IsInNextNMonths([Christmas Bonus Paid], 6)
I can see that everyone gets their Christmas bonus in the next six months apart from John.
Date.IsInPreviousMonth
Date.IsInPreviousMonth(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the previous month, as determined by the current date and time on the system.
I will look if the Last Expenses Paid took place last month:
The M formula I have used is
= Date.IsInPreviousMonth([Last Expenses Paid])
I can see that everyone got their expenses last month apart from Mary (who is either unlucky or bad at filling forms out!).
Date.IsInPreviousNMonths
Date.IsInPreviousNMonths(datetime as any, months as number) as nullable logical
Indicates whether the given datetime occurs during the previous number (months) of months, as determined by the current date and time on the system.
This time I will look at whether Bonus 2017 Paid occurred in the last four (4) months.
The M formula I used for this is:
= Date.IsInPreviousNMonths([Bonus 2017 Paid], 4)
I can see that John received the 2017 bonus in the last 4 months, but the others received theirs before this period.
Date.IsInYearToDate
Date.IsInYearToDate(datetime) as logical
Returns a logical value indicating whether the given datetime occurred in the period starting January 1st of the current year and ending on the current day, as determined by the current date and time on the system.
This is useful for calculating totals to date. In this example I will check if the Current Expenses Paid is in the year to date.
The M formula I have used is:
= Date.IsInYearToDate([Current Expenses Paid])
So I can see everyone has their expenses paid in the year to date apart from Mary!
Date.IsLeapYear
Date.IsLeapYear(datetime as nullable datetime) as nullable logical
Returns a logical value indicating whether the year portion of a datetime value is a leap year.
The remaining functions will be summarised as they are more on the same theme, just with different periods of time.
Date.IsInCurrentDay(datetime as any) as nullable logical
Indicates whether the given datetime occurs during the current day, as determined by the current date and time on the system.
Date.IsInCurrentQuarter(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the current quarter, as determined by the current date and time on the system.
Date.IsInCurrentWeek(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the current week, as determined by the current date and time on the system.
Date.IsInCurrentYear(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the current year, as determined by the current date and time on the system.
Date.IsInNextDay(datetime as any) as nullable logical
Indicates whether the given datetime occurs during the next day, as determined by the current date and time on the system.
Date.IsInNextNDays(datetime as any, days as number) as nullable logical
Indicates whether the given datetime occurs during the next number (days) of days, as determined by the current date and time on the system.
Date.IsInNextNQuarters(datetime as any, quarters as number) as nullable logical
Indicates whether the given datetime occurs during the next number (quarters) of quarters, as determined by the current date and time on the system.
Date.IsInNextNWeeks(datetime as any, weeks as number) as nullable logical
Indicates whether the given datetime occurs during the next number of weeks (weeks), as determined by the current date and time on the system.
Date.IsInNextNYears(datetime as any, years as number) as nullable logical
Indicates whether the given datetime occurs during the next number (years) of years, as determined by the current date and time on the system.
Date.IsInNextQuarter(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the next quarter, as determined by the next date and time on the system.
Date.IsInNextWeek(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the next week, as determined by the next date and time on the system.
Date.IsInNextYear(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the next year, as determined by the next date and time on the system.
Date.IsInPreviousDay(datetime as any) as nullable logical
Indicates whether the given datetime occurs during the previous day, as determined by the current date and time on the system.
Date.IsInPreviousNDays(datetime as any, days as number) as nullable logical
Indicates whether the given datetime occurs during the previous number (days) of days, as determined by the current date and time on the system.
Date.IsInPreviousNQuarters(datetime as any, quarters as number) as nullable logical
Indicates whether the given datetime occurs during the previous number (quarters) of quarters, as determined by the current date and time on the system.
Date.IsInPreviousNWeeks(datetime as any, weeks as number) as nullable logical
Indicates whether the given datetime occurs during the previous number (weeks)of weeks, as determined by the current date and time on the system.
Date.IsInPreviousNYears(datetime as any, years as number) as nullable logical
Indicates whether the given datetime occurs during the previous number (years) of years, as determined by the current date and time on the system.
Date.IsInPreviousQuarter(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the previous quarter, as determined by the current date and time on the system.
Date.IsInPreviousWeek(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the previous week, as determined by the current date and time on the system.
Date.IsInPreviousYear(datetime) as logical
Returns a logical value indicating whether the given datetime occurred during the previous year, as determined by the current date and time on the system.
Come back next time for more ways to use Power Query!
