tableau time intelligence functions.txt

//Current month start date
DATETRUNC("month", [Reference date formula])


//Prev month start date
DATEADD("month", -1, [Current month start date])


//Prev month end date (full month)
[Current month start date] -1


//Prev Month end date (up till same date)
DATEADD("month", -1, [Reference date formula])








//Same month last year start date
DATEADD("year", -1, [Current month start date])


//Same month last year end date (full month)
DATEADD("month", 1, [Same month last year start date]) -1


//Same month last year end date (up till same date)
DATEADD("year", -1, [Reference date formula])







//Current QTR start date
DATETRUNC("quarter", [Reference date formula])


//Prev QTR start date
DATEADD("quarter", -1, [Current QTR start date])


//Prev QTR end date (full qtr)
[Current QTR start date] -1


//Prev QTR end date (up till same date)
DATEADD("quarter", -1, [Reference date formula])








//Same QTR last year start date
DATEADD("year", -1, [Current QTR start date])


//Same QTR last year end date (full qtr)
DATEADD("quarter", 1, [Same QTR last year start date]) -1


//Same QTR last year end date (up till same date)
DATEADD("year", -1, [Reference date formula])









//Current FY start date
If MONTH([Reference date formula]) < 7 THEN 
DATEADD("month", -6, DATETRUNC("year", [Reference date formula])) ELSE
DATEADD("month", 6, DATETRUNC("year", [Reference date formula]))
END


//Prev FY start date
DATEADD("year", -1, [Current FY start date])


//Prev FY end date (full year)
DATEADD("year", 1, [Prev FY start date]) -1


//Prev FY end date (up till same date)
DATEADD("year", -1, [Reference date formula])











###################################################################









//Current month
IF
[date] >= [Current month start date] AND
[date] <= [Reference date formula]
THEN [sales]
END



//Prev month (up till same date)
IF
[date] >= [Prev month start date] AND
[date] <= [Prev month end date (up till same date)]
THEN [sales]
END


//Prev month (full month)
IF
[date] >= [Prev month start date] AND
[date] <= [Prev month end date (full month)]
THEN [sales]
END






//Same Month Last Year (full month)
IF
[date] >= [Same month last year start date] AND
[date] <= [Same month last year end date (full month)]
THEN [sales]
END


//Same Month Last Year (up till same date)
IF
[date] >= [Same month last year start date] AND
[date] <= [Same month last year end date (up till same date)]
THEN [sales]
END











//Current QTR
IF
[date] >= [Current QTR start date] AND
[date] <= [Reference date formula]
THEN [sales]
END



//Prev QTR (up till same date)
IF
[date] >= [Prev QTR start date] AND
[date] <= [Prev QTR end date (up till same date)]
THEN [sales]
END


//Prev QTR (full qtr)
IF
[date] >= [Prev QTR start date] AND
[date] <= [Prev QTR end date (full qtr)]
THEN [sales]
END






//Same QTR Last Year (full qtr)
IF
[date] >= [Same QTR last year start date] AND
[date] <= [Same QTR last year end date (full qtr)]
THEN [sales]
END


//Same QTR Last Year (up till same date)
IF
[date] >= [Same QTR last year start date] AND
[date] <= [Same QTR last year end date (up till same date)]
THEN [sales]
END






















//Current FY
IF
[date] >= [Current FY start date] AND
[date] <= [Reference date formula]
THEN [sales]
END


//Prev FY (full year)
IF
[date] >= [Prev FY start date] AND
[date] <= [Prev FY end date (full year)]
THEN [sales]
END

//Prev FY (up till same date)
IF
[date] >= [Prev FY start date] AND
[date] <= [Prev FY end date (up till same date)]
THEN [sales]
END