cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

First day of next yearly quarter

Hello,  I have field date and I would like the function to calculate the date of first day of the next quarter.

What I mean:

1st quarter - from 01.01. to 01.03.

2nd quarter - from 01.04. to 01.06.

3d quarter - from 01.07. to 01.09.

4th quarter - fro, 01.10. to 01.12

So.... for example:

If the date is 24.05.2019 -> the calculated date must be 01.09.2019

If the date is 21.10.2019 -> the calculated date must be 01.01.2020

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User II
Dual Super User II

Hi @bondy_07 

 

Expressions will be your best bet

 

if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),0), startofmonth(adddays(startofmonth(outputs('Compose')),31)),if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),1), startofmonth(adddays(startofmonth(adddays(startofmonth(adddays(startofmonth(outputs('Compose')),31)),31)),31)),if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),2), startofmonth(adddays(startofmonth(adddays(startofmonth(outputs('Compose')),31)),31)),'')))

 

replace outputs('Compose') - with the dynamic content of your date field

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

5 REPLIES 5
Dual Super User II
Dual Super User II

Hi @bondy_07 

 

Expressions will be your best bet

 

if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),0), startofmonth(adddays(startofmonth(outputs('Compose')),31)),if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),1), startofmonth(adddays(startofmonth(adddays(startofmonth(adddays(startofmonth(outputs('Compose')),31)),31)),31)),if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),2), startofmonth(adddays(startofmonth(adddays(startofmonth(outputs('Compose')),31)),31)),'')))

 

replace outputs('Compose') - with the dynamic content of your date field

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

@RezaDorrani  Thank you! It works perfectly, but could you please explain me what mean the every single command, just want to understand the code.

Thank you again!

@bondy_07 

 

if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),0), startofmonth(adddays(startofmonth(outputs('Compose')),31)),if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),1), startofmonth(adddays(startofmonth(adddays(startofmonth(adddays(startofmonth(outputs('Compose')),31)),31)),31)),if(equals(mod(int(formatDateTime(outputs('Compose'),'MM')),3),2), startofmonth(adddays(startofmonth(adddays(startofmonth(outputs('Compose')),31)),31)),'')))

 

The if conditions get the current month and divide it by 3 which returns 1, 2 or 0

1- Jan, Apr, July, Oct  - for this we need to go 3 months ahead

2 - Feb, May, Aug, Nov - for this we need to go 2 months ahead

3 - Mar, June, Sep, Dec - for this we need to go 1 months ahead

 

 

 

@RezaDorraniGreat! Thank you very much! 

@RezaDorrani 

May I ask you one more question...

I need to set date - first day of july 

For example:

If the date is to 31.06.2019 -> the date must be 01.07.2019 (the same year)

If the date is after 01.07.2019 -> the date must be 01.07.2020 (next year)

I`ve tried this code, but just want to ask you: Is it ok and is there easier way ?


if(equals(int(formatDateTime(outputs('Compose'),'MM')),1),startofmonth(adddays(startofmonth(outputs('Compose')),186)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),2),startofmonth(adddays(startofmonth(outputs('Compose')),155)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),3),startofmonth(adddays(startofmonth(outputs('Compose')),124)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),4),startofmonth(adddays(startofmonth(outputs('Compose')),93)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),5),startofmonth(adddays(startofmonth(outputs('Compose')),62)),
if(equals(int(formatDateTime(outputs('Compose')],'MM')),6),startofmonth(adddays(startofmonth(outputs('Compose')),31)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),7),startofmonth(adddays(startofmonth(outputs('Compose')),372)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),8),startofmonth(adddays(startofmonth(outputs('Compose')),341)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),9),startofmonth(adddays(startofmonth(outputs('Compose')),310)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),10),startofmonth(adddays(startofmonth(outputs('Compose')),279)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),11),startofmonth(adddays(startofmonth(outputs('Compose')),248)),
if(equals(int(formatDateTime(outputs('Compose'),'MM')),12),startofmonth(adddays(startofmonth(outputs('Compose')),186)),''))))))))))))

 

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (6,311)