cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Projection for current quarter

I am currently trying to make a projection for the current quarter and I have the following:

 

John Projected Current Quarter Gross Revenue = ([John Gross FCI Current Quarter]/(TODAY()-STARTOFQUARTER('FCI'[PaymentDate].[Date]))*(365.25/4)))

 

Where John Gross FCI Current Quarter = TOTALQTD([John Gross Fees],'FCI'[PaymentDate])

 

But it wont work, I cant for the life of me work out how to basically take the income for the current quarter, then get the dates between today and the current quarter to be able to multiply it up to project what is likely to come in for the quarter. I think it is the 'Startofquarter' that isnt working for me.

 

Any help would be much appreciated!

 

Many thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Return the date of the start of the quarter

Hi Lydia,

 

Thanks for getting back to me. I managed to solve this in the end by writing the following formula......

 

First day of Quarter = DATE(YEAR(TODAY()),IF(AND(MONTH(TODAY())<4,MONTH(TODAY())>0),1,IF(AND(MONTH(TODAY())<7,MONTH(TODAY())>3),4,IF(AND(MONTH(TODAY())<10,MONTH(TODAY())>6),7,10))),1)

 

I appreciate it is a bit clunky but it seems to work!!

 

Thanks for your help!

View solution in original post

4 REPLIES 4
Highlighted
Microsoft
Microsoft

Re: Projection for current quarter

@alexsimpson,

Do you have a calendar table? I would recommend you create relationship using date field of the calendar table and PaymentDate field of your original table, then change the DAX formulas to the following, and there is an example for your reference.

John Gross FCI Current Quarter = TOTALQTD([John Gross Fees],'Calendar'[DateKey])
John Projected Current Quarter Gross Revenue = ([John Gross FCI Current Quarter]/(TODAY()-STARTOFQUARTER('Calendar'[DateKey]))*(365.25/4))

1.PNG


If the above DAX doesn't help, please share dummy data of your table and post expected result here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Return the date of the start of the quarter

Hi, so I really only need to return the date of the start of the quarter to work. i.e. the current date is 21/04/2018 so I want the date of 01/04/2018 to be returned as a date. If the date was the 20/03/2018 then 01/01/2018 would be returned. 

 

I have tried STARTOFQUARTER but on the data set it is returning 29/11/2011 which obviously is not running on today's date and isnt even the date of the start of a quarter. 

 

Is this possible?

 

Many thanks in advance.

 

Alex

Highlighted
Microsoft
Microsoft

Re: Return the date of the start of the quarter

@alexsimpson,

 

STARTOFQUARTER  function works in my scenario. I would recommend you create a calendar table, create relationship between calendar table and your original table. Then create column as shown in my screenshot to test the function. If you still have issues, please share sample data of your table for us to test.

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Return the date of the start of the quarter

Hi Lydia,

 

Thanks for getting back to me. I managed to solve this in the end by writing the following formula......

 

First day of Quarter = DATE(YEAR(TODAY()),IF(AND(MONTH(TODAY())<4,MONTH(TODAY())>0),1,IF(AND(MONTH(TODAY())<7,MONTH(TODAY())>3),4,IF(AND(MONTH(TODAY())<10,MONTH(TODAY())>6),7,10))),1)

 

I appreciate it is a bit clunky but it seems to work!!

 

Thanks for your help!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (778)