Get an Array of Month and End Dates resulted from between a Start Date and End Date

[ARAlmac]

Hi Power Automate Community,

this is my first power automate question!

I have a bit of a difficult scenario for a Power Automate flow and need some help.

I have a Revised Start Date and a Revised End Date column from Dataverse. Specifically in the flow: 
triggerOutputs()?['body/crab0_revisedstartdate']

triggerOutputs()?['body/crab0_revisedenddate']

I have been asked to create monthly milestones for each month between the 2 dates specified. For example: 
31/01/2029 

To

31/12/2029.

This is a difference of 12 months, so I should have 12 milestones to create.

 Each milestone must have:
"Month Name": January e.g.

"Month End Date": 31/01/2029 e.g.

I think I need to create an array of some sort based on the months between those dates but cant quite crack it.

Any help would be really appreciated!

[Chriddle · Accepted solution]

Maybe not the most elegant way, but it seems to work:

Date (Compose)

{
  "Start": "2029-01-31",
  "End": "2029-12-31"
}

Select

From

range(
	0,
	add(
		if(
			contains(
				dateDifference(outputs('Date')['Start'], outputs('Date')['End']),
				'.'
			),
			int(
				first(
					split(
						dateDifference(outputs('Date')['Start'], outputs('Date')['End']),
						'.'
					)
				)
			),
			0
		),
		1
	)
)

Map

formatDateTime(	
	addDays(
		outputs('Date')['Start'],
		item()
	),
	'yyyy-MM-01'
)

Compose 2

union(
	body('Select'),
	json('[]')
)

Select 2

From

outputs('Compose_2')

Map Month Name

formatDateTime(item(), 'MMMM', 'us-en')

Map Month End Date

addDays(
	startOfMonth(
		addDays(
			item(),
			31
		)
	),
	-1,
	'MM/dd/yyyy'
)

Result:

[
  {
    "Month Name": "January",
    "Month End Date": "01/31/2029"
  },
  {
    "Month Name": "February",
    "Month End Date": "02/28/2029"
  },
  {
    "Month Name": "March",
    "Month End Date": "03/31/2029"
  },
  {
    "Month Name": "April",
    "Month End Date": "04/30/2029"
  },
  {
    "Month Name": "May",
    "Month End Date": "05/31/2029"
  },
  {
    "Month Name": "June",
    "Month End Date": "06/30/2029"
  },
  {
    "Month Name": "July",
    "Month End Date": "07/31/2029"
  },
  {
    "Month Name": "August",
    "Month End Date": "08/31/2029"
  },
  {
    "Month Name": "September",
    "Month End Date": "09/30/2029"
  },
  {
    "Month Name": "October",
    "Month End Date": "10/31/2029"
  },
  {
    "Month Name": "November",
    "Month End Date": "11/30/2029"
  },
  {
    "Month Name": "December",
    "Month End Date": "12/31/2029"
  }
]

[ARAlmac]

@Chriddle ,
Very impressive! It seems to work a treat.. I also extended the date range to 2 years and it picks up the 2030 dates perfectly. I must admit, I would love a breakdown of how you knew to use range(), union() etc!

Thank you so much 🙂 

[Chriddle]

@ARAlmac Simply by reading the manual and of course by reading this forum 😉

There are not that many expression functions https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference

So with some programming experience, you can easily learn them within a few hours.

If you have questions about specific parts of the above expressions, go ahead 😊