How to filter by Month/Year taking into consideration if the year is a leap year?

[Julien2]

Hello,

I have an SP list that has a Date column (full date format: MM/dd/yyyy) with a bunch of data. 

I am planning to build a flow to filter the data based on month/year against a full date format.

Example: If I want to see data for August 2022, when the parameters are passed to the flow it should filter the dates between 08/1/2022 and 08/31/2022.

Taking into consideration to determine if a year is a leap year.

Would it be possible to show an example of how we can do that with power automate?

Any help is highly appreciated.

Best regards,
Julien

[DamoBird365]

Hey @Julien2 

I've got a couple of options for you.  To get the the first and last day of this (i.e. utcnow()) month you can try the following:

Start: startOfMonth(utcnow())

End: adddays(addToTime(outputs('Compose'),1,'Month'),-1)

Then if you want all of the start / end dates for all 12 months of 2022, you could try a select which will output the following array:

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

From: range(1,12)

Start: formatDateTime(concat('2022-',item(),'-01'),'yyyy-MM-dd')

End: adddays(addToTime(formatDateTime(concat('2022-',item(),'-01'),'yyyy-MM-dd'),1,'Month'),-1,'yyyy-MM-dd')

Month: formatDateTime(concat('2022-',item(),'-01'),'MMMM')

Let me know if that works for you.  I also have a video on Dates in Power Automate that might be worth taking a look at https://youtu.be/ybPWtu1i1j0

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts. Thanks

[DamoBird365 · Accepted solution]

Hi @Julien2 

Just tried it on 2020, by inserting a compose with the year.  

Start: formatDateTime(concat(outputs('ComposeYear'),'-',item(),'-01'),'yyyy-MM-dd')

End: adddays(addToTime(formatDateTime(concat(outputs('ComposeYear'),'-',item(),'-01'),'yyyy-MM-dd'),1,'Month'),-1,'yyyy-MM-dd')

Month: formatDateTime(concat(outputs('ComposeYear'),'-',item(),'-01'),'MMMM')

Output was:

[
  {
    "Start": "2020-01-01",
    "End": "2020-01-31",
    "Month": "January"
  },
  {
    "Start": "2020-02-01",
    "End": "2020-02-29",
    "Month": "February"
  },
  {
    "Start": "2020-03-01",
    "End": "2020-03-31",
    "Month": "March"
  },
  {
    "Start": "2020-04-01",
    "End": "2020-04-30",
    "Month": "April"
  },
  {
    "Start": "2020-05-01",
    "End": "2020-05-31",
    "Month": "May"
  },
  {
    "Start": "2020-06-01",
    "End": "2020-06-30",
    "Month": "June"
  },
  {
    "Start": "2020-07-01",
    "End": "2020-07-31",
    "Month": "July"
  },
  {
    "Start": "2020-08-01",
    "End": "2020-08-31",
    "Month": "August"
  },
  {
    "Start": "2020-09-01",
    "End": "2020-09-30",
    "Month": "September"
  },
  {
    "Start": "2020-10-01",
    "End": "2020-10-31",
    "Month": "October"
  },
  {
    "Start": "2020-11-01",
    "End": "2020-11-30",
    "Month": "November"
  },
  {
    "Start": "2020-12-01",
    "End": "2020-12-31",
    "Month": "December"
  }
]

Hope that makes sense 👍 and of course you could make the year dynamic by using formatDateTime(utcnow(),'yyyy') in the compose.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts. Thanks

[Julien2]

Hello @DamoBird365 ,

I would like to thank you for taking the time to explain all the steps in detail with screenshots.

I just have two questions about the second option you've provided.
I see that you passed the year dynamically, but I also have the month which will be a dynamic output by the trigger or the get item action.

In the select action, what should be changed if the month is also dynamic?

Assume that the user from the UI selected to filter as per the following parameters:

• Month: March
• Year: 1998

After the output result of the select action I just need to get this date range, which means I have to apply a filter array and the condition can be:

• Filter Where Month (from the select action) is equal to the month selected by the user (from the manual trigger), then I will get the desired start/end dates and I can apply my filter in the next action for the get items.

Is that will work right?

I will give it a try and get back to you with feedback after doing my testing, and please let me know what will be the updated expression for the dynamic Month param.

Thank you!!

[Julien2]

Hello @DamoBird365 ,

I was able to test it and get the same results as yours with the second option.

I was wondering if we are able with the first option to get what we need directly without going with the select action.

I added two compose actions "Month" and "Year" as below:

I am still stuck with the expression that we should build to get the date range for those two parameters passed. (Month and Year dynamically)

Could you please provide an example of this?

Awaiting your response.

Best regards,
Julien

[DamoBird365 · Accepted solution]

Hi @Julien2 

If you're not looking to filter, you could try the following:

Start: formatDateTime(concat(outputs('ComposeYear'),'-',outputs('ComposeMonth'),'-01'),'yyyy-MM-dd')

End: adddays(addToTime(outputs('ComposeStart'),1,'Month'),-1,'yyyy-MM-dd')

Here's the output:

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts. Thanks

[Julien2]

Hello @DamoBird365 ,

I appreciate you providing this example.

This worked perfectly for me as well!

I would like to thank you once again for your assistance.

Best regards,
Julien