This is the solution of how to calculate the QTD, in continuation of the article : Calculate MTD, QTD and YTD Values

If the dim date is a base table you'll need to add the following 3 invisible fields:

1. FirstDayofQ:

CASE WHEN GetQuarter(Date) = 1 THEN CreateDate(getyear(Date),1,1)

WHEN GetQuarter(Date) = 2 THEN CreateDate(getyear(Date),4,1)

WHEN GetQuarter(Date) = 3 THEN CreateDate(getyear(Date),7,1)

WHEN GetQuarter(Date) = 4 THEN CreateDate(getyear(Date),10,1) END

2. QTDMonthInd:

CASE WHEN GetMonth(Date) IN(1,4,7,10) THEN 1

WHEN GetMonth(Date) IN(2,5,8,11) THEN 2

WHEN GetMonth(Date) IN(3,6,9,12) THEN 3 END

3. QTDMonthIndNow:

CASE WHEN GetMonth(now()) IN(1,4,7,10) THEN 1

WHEN GetMonth(now()) IN(2,5,8,11) THEN 2

WHEN GetMonth(now()) IN(3,6,9,12) THEN 3 END

Now add the **isQTD** field which is the actual flag you would use:

CASE WHEN

(QTDMonthInd < QTDMonthIndNow and x.Date >= FirstDayofQ) or

(QTDMonthInd = QTDMonthIndNow AND getday(Date)<=getday(now()))

THEN 1 ELSE 0 END

If the dim date is already a custom table then you would need to use and modify this code:

select x.date,

CASE WHEN

(x.QTDMonthInd < x.QTDMonthIndNow and x.date >= x.FirstDayofQ) or

(x.QTDMonthInd = x.QTDMonthIndNow AND getday(x.date)<=getday(now()))

THEN 1 ELSE 0 END AS isQTD

from (

select distinct date,

CASE WHEN GetQuarter(a.Date) = 1 THEN CreateDate(getyear(a.Date),1,1)

WHEN GetQuarter(a.Date) = 2 THEN CreateDate(getyear(a.Date),4,1)

WHEN GetQuarter(a.Date) = 3 THEN CreateDate(getyear(a.Date),7,1)

WHEN GetQuarter(a.Date) = 4 THEN CreateDate(getyear(a.Date),10,1)

END AS FirstDayofQ,

CASE WHEN GetMonth(a.Date) IN(1,4,7,10) THEN 1

WHEN GetMonth(a.Date) IN(2,5,8,11) THEN 2

WHEN GetMonth(a.Date) IN(3,6,9,12) THEN 3

END AS QTDMonthInd,

CASE WHEN GetMonth(now()) IN(1,4,7,10) THEN 1

WHEN GetMonth(now()) IN(2,5,8,11) THEN 2

WHEN GetMonth(now()) IN(3,6,9,12) THEN 3

END AS QTDMonthIndNow

fromtableAas a

union

select distinct date,

CASE WHEN GetQuarter(b.Date) = 1 THEN CreateDate(getyear(b.Date),1,1)

WHEN GetQuarter(b.Date) = 2 THEN CreateDate(getyear(b.Date),4,1)

WHEN GetQuarter(b.Date) = 3 THEN CreateDate(getyear(b.Date),7,1)

WHEN GetQuarter(b.Date) = 4 THEN CreateDate(getyear(b.Date),10,1)

END AS FirstDayofQ,

CASE WHEN GetMonth(b.Date) IN(1,4,7,10) THEN 1

WHEN GetMonth(b.Date) IN(2,5,8,11) THEN 2

WHEN GetMonth(b.Date) IN(3,6,9,12) THEN 3

END AS QTDMonthInd,

CASE WHEN GetMonth(now()) IN(1,4,7,10) THEN 1

WHEN GetMonth(now()) IN(2,5,8,11) THEN 2

WHEN GetMonth(now()) IN(3,6,9,12) THEN 3

END AS QTDMonthIndNow

fromtableBas b ) as x

If you are using the date dimension file and you wish to write a custom query on top of it, you can use the following code:

SELECT *

CASE WHEN

(x.QTDMonthInd < x.QTDMonthIndNow and x.Date >= x.FirstDayofQ) or

(x.QTDMonthInd = x.QTDMonthIndNow AND getday(x.Date)<=getday(now()))

THEN 1 ELSE 0 END AS isQTD

FROM

(

SELECT*,

CASE WHEN GetQuarter(d.Date) = 1 THEN CreateDate(getyear(d.Date),1,1)

WHEN GetQuarter(d.Date) = 2 THEN CreateDate(getyear(d.Date),4,1)

WHEN GetQuarter(d.Date) = 3 THEN CreateDate(getyear(d.Date),7,1)

WHEN GetQuarter(d.Date) = 4 THEN CreateDate(getyear(d.Date),10,1)

END AS FirstDayofQ,

CASE WHEN GetMonth(d.Date) IN(1,4,7,10) THEN 1

WHEN GetMonth(d.Date) IN(2,5,8,11) THEN 2

WHEN GetMonth(d.Date) IN(3,6,9,12) THEN 3

END AS QTDMonthInd,

CASE WHEN GetMonth(now()) IN(1,4,7,10) THEN 1

WHEN GetMonth(now()) IN(2,5,8,11) THEN 2

WHEN GetMonth(now()) IN(3,6,9,12) THEN 3

END AS QTDMonthIndNow

FROM Dim_date_CSV d

) AS x

In this case, replace the inner * with the list of the fields you actually need from the date dimension file.