If you want to do some custom Time Intelligence (like below) or create Calculated Columns in a Date table then this Cheat Sheet can be very handy. Don’t have a date table yet? Then first read this article: https://ilovepowerbi.tips/2019/11/03/make-a-date-table-in-power-bi-in-just-5-minutes/

[Custom Time Intelligence Measure] =
VAR startDateActivePeriod = MIN ( Dates[date] ) –Get start of active Month, Quarter, Year etc. within Filter Context
VAR
start = — Do some fancy Date Calculations here to calculate the startDate
VAR end = — and here to calculate the endDate
RETURN
CALCULATE ( [Revenue], DATESBETWEEN ( Dates[date], start, end ) )

## Common Date Calculations:

–Current Period
VAR
currentDate = TODAY ()
VAR startDateActivePeriod = MIN ( Dates[Date] ) –Get start of active Month, Quarter, Year etc. within Filter Context
VAR endDateActivePeriod = MAX ( Dates[Date] ) –Get end of active Month, Quarter, Year etc. within Filter Context
VAR maxOrderDateOverall = CALCULATE ( MAX ( Order[OrderDate] ), ALL ( Dates ) ) — Max OrderDate not filtered by current Filter Context.

VAR currentDateYear = YEAR ( currentDate )
VAR currentDateQuarter = VALUE ( FORMAT ( currentDate, “Q” ) )
VAR currentDateMonth = MONTH ( currentDate )
VAR currentDateWeek = WEEKNUM ( currentDate ) — First of january is always weeknr 1
VAR currentDateISOWeek = WEEKNUM ( currentDate, 21 ) — First week with 4 days is week 1, starting on Monday

–Day calculations
VAR yesterday = currentDate1
VAR n = 120
VAR nDaysBackDate = currentDaten

–Month calculations
VAR currentMonthStart = DATE ( currentDateYear, currentDateMonth, 1 )
VAR currentMonthEnd = EOMONTH ( currentDate, 0 )
VAR x = 12
VAR xMonthsBackStart = DATE ( currentDateYear, currentDateMonthx, 1 )
VAR xMonthsBackEnd = EOMONTH ( currentDate, – x )

–Quarter calculations
VAR firstMonthOfCurrentQuarter = currentDateQuarter * 32
VAR currentQuarterStart = DATE ( currentDateYear, firstMonthOfCurrentQuarter, 1 )
VAR currentQuarterEnd = EOMONTH ( currentQuarterStart, 2 )
VAR y = 4
VAR yQuartersBackStart = DATE ( currentDateYear, firstMonthOfCurrentQuarter3 * y, 1 )
VAR yQuartersBackEnd = EOMONTH ( currentQuarterStart, – 3 * y )

–Year calculations
VAR currentYearStart = DATE ( currentDateYear1, 1, 1 )
VAR currentYearEnd = DATE ( currentDateYear1, 12, 31 )
VAR z = 2
VAR zYearsBackStart = DATE ( currentDateYearz, 1, 1 )
VAR zYearsBackEnd = DATE ( currentDateYearz, 12, 31 )

–Week calculations
VAR currentWeekStart = currentDateWEEKDAY ( currentDate, 2 ) + 1
VAR currentWeekEnd = currentWeekStart + 6
VAR w = 18
VAR wWeeksBackStart = currentDateWEEKDAY ( currentDate, 2 )y * 7 + 1
VAR wWeeksBackEnd = currentDateWEEKDAY ( currentDate, 2 ) ( y1 ) * 7

## What do you think?

Which formula’s do you use a lot to do custom Time Intelligence or Date Calculations? Do you have other formulas we should add?