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:
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.
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])
Thanks for reply.
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?
If yes. please suggest.
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?
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
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.