Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save Tadge-Analytics/6d81311639211231da7af81f04256c95 to your computer and use it in GitHub Desktop.
Save Tadge-Analytics/6d81311639211231da7af81f04256c95 to your computer and use it in GitHub Desktop.
//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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment