Forum Discussion

alexsimpson's avatar
alexsimpson
Icon for Helper I rankHelper I
8 years ago
Solved

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()-STARTOFQUART...
Need Help
  • alexsimpson's avatar
    alexsimpson
    8 years ago

    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!

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
alexsimpson
Icon for Helper I rankHelper I
8 years ago

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!