Forum Discussion

harrisward's avatar
harrisward
New Member
4 years ago

Dynamically Grouping Months Based on Todays Month

Hey folks. I have a problem where I need to create a new column on my date table (dimension) using Power Query that will group my dates into 3 month "artificial" quarters based on the month we are cu...
Need Help
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
4 years ago

harrisward,

 

Try this custom column in Power Query:

 

if Date.IsInCurrentMonth([Date]) then
  "Month 0"
else if [Date]
  >= Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), -3) and [Date]
  <= Date.AddDays(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), -1)
then
  "Month 1 to 3"
else if [Date]
  >= Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), - 6) and [Date]
  <= Date.AddDays(Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), -3), -1)
then
  "Month 4 to 6"
else if [Date]
  >= Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), - 9) and [Date]
  <= Date.AddDays(Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), -6), -1)
then
  "Month 7 to 9"
else if [Date]
  >= Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), - 12) and [Date]
  <= Date.AddDays(Date.AddMonths(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())), -9), -1)
then
  "Month 10 to 12"
else
  null