Projection for current quarter

[alexsimpson]

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!

[Anonymous]

@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))

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

Regards,
Lydia

[alexsimpson]

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

[Anonymous]

@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.



Regards,
Lydia

[alexsimpson · Accepted solution]

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!