cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nich3play3r
Advocate I
Advocate I

Trigger for first day of fiscal quarter

Hi.

 

I have a paginated report I'd like to execute on the first day of the fiscal quarter (fiscal year starts July 1). What's the most elegant way to code that in Power Automate? I have a dim_date table in my warehouse that has a handy "Quarters Since" field that I'm trying to replicate in my flow as a trigger. Maybe since I'm only dealing with 4 dates, I just have the flow check to see if the current date is IN (one of the dates)?

 

Thanks for any thoughts you might have on the matter!

1 ACCEPTED SOLUTION

Accepted Solutions

Here is one way to do it.  You can check if the current date (formatted as MMdd) is in an array of values (for the start of each fiscal quarter).  You can put this expression in a Condition step (and test for true).

 

contains(createArray('0101','0401','0701','1001'), formatDateTime(utcNow(), 'MMdd'))

 

Or, you could put this expression in the trigger condition for your Schedule trigger (in the Settings, add a trigger condition and put the expression above with an @ in front.  That way, it should only actually fire 4 times/year (instead of daily).  You can put today's date in MMdd (for UTC) in your createArray to test it.

 

@contains(createArray('0101','0401','0701','1001'), formatDateTime(utcNow(), 'MMdd'))

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
nich3play3r
Advocate I
Advocate I

Noodling more on this, I now have a flow started with a recurrence step to run daily. Then a Condition step where I'm now thinking I can do something like:

 

month(utcnow()) = 7 and day(utcnow()) = 1

or

month(utcnow()) = 10 and day(utcnow()) = 1

or

month(utcnow()) = 1 and day(utcnow()) = 1

or

month(utcnow()) = 4 and day(utcnow()) = 1

 

Just struggling a bit now with figuring out the formatting for the datepart stuff for utcnow....

nich3play3r
Advocate I
Advocate I

So I initialized a variable for month of current date:

int(formatDateTime(utcNow(), 'MM'))

 

Then my conditional step looks like this. utcNow = startOfMonth and month of utcNow in 1, 4, 7, or 10. It returns false as of today, which is right.... So much for "elegant".... 😂

 

2021-11-14 16_26_51-Edit your flow _ Power Automate.png

Here is one way to do it.  You can check if the current date (formatted as MMdd) is in an array of values (for the start of each fiscal quarter).  You can put this expression in a Condition step (and test for true).

 

contains(createArray('0101','0401','0701','1001'), formatDateTime(utcNow(), 'MMdd'))

 

Or, you could put this expression in the trigger condition for your Schedule trigger (in the Settings, add a trigger condition and put the expression above with an @ in front.  That way, it should only actually fire 4 times/year (instead of daily).  You can put today's date in MMdd (for UTC) in your createArray to test it.

 

@contains(createArray('0101','0401','0701','1001'), formatDateTime(utcNow(), 'MMdd'))

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Thank you for this, @mahoneypat ; that's the elegance I was looking for! I'd come across that trigger settings option but kept getting a formatting error...now I see what I was doing wrong there. That array bit is very sweet indeed. This is great!

jleggett81
New Member

Trying to do something similar.  Have a SP list with a status(choice), shipped date(calculated value based on status), and quarter shipped(choice) columns.  I'm wanting to update quarter shipped based on ship date.  So if shipped date says 12/13/2021, the flow will update quarter shipped with the 1st quarter choice,

 

I know I need an expression that formats the ship date into a month number(MM or MMdd).  Maybe use a Compose data operation.(Still need to figure that out) Then based on above it looks like I can create a Condition that tests the previous step against an array or arrays.

 

Does this sound right?  Or am I missing something?

 

Still have to actually try some of this, just trying to put together some basic logic for it before spending a bunch of time on it.

 

Jonathan

@jleggett81 do you even need an array for this? It sounds like the value in the quarter shipped column is simply always a formatted version of the date shipped value; there won't be any value in quarter shipped if date shipped is empty, right? Might something like this help:

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

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,792)