Forum Discussion

alexsimpson's avatar
alexsimpson
Helper 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,

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

    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'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
        Helper 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!