6 Replies Latest reply on Jan 23, 2019 10:18 AM by Joe Oppelt

# How to fix quarter date for calculation

Hi All,

I need a help regarding calculation of quarter date highlighted in yellow color. Can anyone help me in fixing the quarter date for each row level so that the  difference between quarter date and actual date can be done.

Thanks and Regards,

Sriku

• ###### 1. Re: How to fix quarter date for calculation

What do you mean by "fixing the quarter date"?

If you are looking to get a constant start-of-quarter date for all rows, do this:

DATETRUNC('quarter',[Actual Date])

That will take every date, and create a new date in the calc that is the first day in the respective quarter for that date.  So any date in January or February or March will result in a value of January 01 for the respective year, and any date in April through June will result in April 01.  Etc.

• ###### 2. Re: How to fix quarter date for calculation

And then

DATEDIFF('day', [Quarter Date Calc], [Actual Date])

will tell you the number of days from the start of the quarter for each actual date.

And if you wanted to, you could nest all the date functions into one complex calc:

DATEDIFF('day', DATETRUNC('quarter',[Actual Date]), [Actual Date])

• ###### 3. Re: How to fix quarter date for calculation

Hi Joe,

Here in the data I have showed the example. The objective is to calculate the quarter date. Is it possible to calculate through LOD function?

Regards,

Sriku

• ###### 4. Re: How to fix quarter date for calculation

I don't understand what you mean by calculate the quarter date.

If you have a date value of August 15, what would you expect the quarter date to be?

• ###### 5. Re: How to fix quarter date for calculation

if we have date value of Aug 15 then it should be considered quarter 3.

 Q1 Jan Feb Mar Q2 Apr May Jun Q3 Jul Aug Sep Q4 Oct Nov Dec

Regards,

Sriku

• ###### 6. Re: How to fix quarter date for calculation

OK.  That's a totally different question from what you asked originally.

Use DATEPART('quarter', [Date field])

(Or DATENAME('quarter ...) )

Both return a one-character value of 1-through-4.  Datepart returns it as an integer.  Datename returns it as a string.

You'll have to format a "Q" in front of the results if that's what you need.

But I don't know how you would use that to do what you asked in the original question:  "... so that the  difference between quarter date and actual date can be done."  You need an actual date value to get a difference between the quarter value and the actual value.