Calculate Quarter for a Date in Power Automate

A common request in my company is for dates to be expressed in relation to the Quarter of the year in which they fall. Using Power Automate there is no native way of calculating this, so in this post I’m demonstrating two different methods to calculate the Quarter from a given input date:

1. Nested IF Conditions
2. Filtered Array

Nested IF Conditions

1. Trigger Flow – for the purposes of this demo I’m using a manual trigger with a date input, but of course it will work with any date so the logic can be implemented within a bigger flow

2. Calculate Month Number – the next step is to use a FormatDateTime function to convert the input date to the Month Number. We use the following expression to achieve this

formatdatetime(triggerBody()['date'], 'MM')

3. Calculate Quarter Number – the final step is to use the calculated Month number to work out which Quarter it is in. This is achieved with a set of nested IF conditions. The expression to use for this is:

if(
	or(
		equals(outputs('MonthNum'), '01'), 
		equals(outputs('MonthNum'), '02'), 
		equals(outputs('MonthNum'), '03')
		), 
	'Q1', 
	if(
		or(
			equals(outputs('MonthNum'), '04'), 
			equals(outputs('MonthNum'), '05'), 
			equals(outputs('MonthNum'), '06')
		), 
		'Q2', 
		if(
			or(
				equals(outputs('MonthNum'), '07'), 
				equals(outputs('MonthNum'), '08'), 
				equals(outputs('MonthNum'), '09')
			), 
			'Q3', 
			if(
				or(
					equals(outputs('MonthNum'), '10'), 
					equals(outputs('MonthNum'), '11'), 
					equals(outputs('MonthNum'), '12')
				), 
				'Q4', 
				'N/A'
			)
		)
	)
)

Filtered Array

1. Trigger Flow – as in the previous method, I’m using a manual trigger for the Flow with a Date input

2. Calculate Month Number – as in the previous method, the first action is to use a FormatDateTime expression to calculate Month Number from the input date

3. Compose Quarter Array – next we compose an Array that contains the Month Numbers and their related Quarter Number. The Array I used is:

[
  {
    "Month": "01",
    "Quarter": "Q1"
  },
  {
    "Month": "02",
    "Quarter": "Q1"
  },
  {
    "Month": "03",
    "Quarter": "Q1"
  },
  {
    "Month": "04",
    "Quarter": "Q2"
  },
  {
    "Month": "05",
    "Quarter": "Q2"
  },
  {
    "Month": "06",
    "Quarter": "Q2"
  },
  {
    "Month": "07",
    "Quarter": "Q3"
  },
  {
    "Month": "08",
    "Quarter": "Q3"
  },
  {
    "Month": "09",
    "Quarter": "Q3"
  },
  {
    "Month": "10",
    "Quarter": "Q4"
  },
  {
    "Month": "11",
    "Quarter": "Q4"
  },
  {
    "Month": "12",
    "Quarter": "Q4"
  }
]

4. Filter Quarter Array – next we use a Filter Array action to find the Array element where the Month Number is equal to the Month Number we calculated in Step 2. If we edit the Filter Array action in Advanced Mode, the expression we use is:

@equals(item()?['month'], outputs('MonthNum'))

5. Compose Quarter – the final step in the Flow is to use a compose action to extract the quarter number from the Array element we returned from the previous step. We do that by using the following expression:

body('Filter_Quarter_array')[0]?['Quarter']

Conclusion

This post should demonstrate that we can use different methods to achieve the same outcomes in Power Automate. If you’d like to download the Flows to test them for yourself then click the links below:

Original Post https://ryanmaclean365.com/2020/06/26/calculate-quarter-for-a-date-in-power-automate/