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 = currentDate – 1
VAR n = 120
VAR nDaysBackDate = currentDate – n

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

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

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

–Week calculations
VAR currentWeekStart = currentDate – WEEKDAY ( currentDate, 2 ) + 1
VAR currentWeekEnd = currentWeekStart + 6
VAR w = 18
VAR wWeeksBackStart = currentDate – WEEKDAY ( currentDate, 2 ) – y * 7 + 1
VAR wWeeksBackEnd = currentDate – WEEKDAY ( currentDate, 2 ) – ( y – 1 ) * 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?

Put your thoughts in the comments below!