• Marek Vavrovic

Time intelligence functions, part 02

Updated: Sep 26

DATEADD, DATESBETWEEN ,DATESINPERIOD

DATESMTD,DATESYTD (cumulative total functions, fiscal year)



syntax

DATEADD(<dates>,<number_of_intervals>,<interval>)

Moves the given set of dates by a specified time interval.


Example 1


DATEADD

calculating Last month sales and variation.


LAST MONTH SALES

CALCULATE = (SUM(FactSales[Sales]),DATEADD(DimDates[Date],-1,MONTH))


Using month granularity. DATEADD(DimDates[Date],-1,MONTH)) shitted all the daily sales one month lower. If you would be using different granularity to display the data, you get a different result back

Example 1


DATEADD(DimDates[Date],-1,QUARTER)) is shifting the sales 1 quarter lower.


DATESBETWEEN


Returns the dates between two given dates.


syntax

DATESBETWEEN(<dates>, <start_date>, <end_date>)


Parameters


Term Definition

dates A date column.

start_date A date expression.

end_date A date expression.


Example


I want to display the Sales for the 1. quarter of 2019.


Quarter 1 2019 Sales = CALCULATE(SUM(FactSales[Sales]), DATESBETWEEN(DimDates[Date],DATE(2019,1,1),DATE(2019,4,30)))

Using variables


DATESINPERIOD

Returns the dates from the given period


syntax

DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)

Example

The following expression evaluates the measure Sales Amount in the last 12 months starting from the last day of the period in the filter context.

two measures involved

1> Sales(YTD) = CALCULATE(SUM(FactSales[Sales]),DATESYTD(DimDates[Date]))

2> Sales Moving Annual Total = CALCULATE ( [_Sales(YTD)], DATESINPERIOD ( DimDates[Date], MAX ( DimDates[Date] ), -1, YEAR))

Example

This measure start at 31.jun 2020, which is Quarte 2 2020 and goes back to Quarter 2 2019.

Example

Calculating 1 year moving average


1 year moving average = CALCULATE( AVERAGE(FactSales[Sales]), DATESINPERIOD(FactSales[Date],LASTDATE(FactSales[Date]),-1,YEAR))



DATESMTD

Returns a set of dates in the month up to the last date visible in the filter context

Cumulative total in each month.

syntax

DATESMTD(<dates>)


Example

DATEMTD creates a group by month and calculates the cumulative total. As I have just 2 records per month, using the dates from the fact table (picture 1) On picture 2 you can see how the data looks like if your fact table is really tiny. But this is not a real life scenario.

Example

Calculating the cumulative total in the month group and starting from the quarter 2.

Example


In this example I am retrieving the value of the DATESMTD function, the cumulative sum, on some specific date (15.jan.2019)


DATESMTD IS EQUAL TO DATE = CALCULATE( CALCULATE(SUM(FactSales[Sales]), DATESMTD(FactSales[Date])), FactSales[Date]=DATE(2019,1,15))

DATESYTD

Returns a set of dates in the year up to the last date visible in the filter context.


Example

calculate Cumulative running total for the sale column.


Example


I want to display the Sales starting from 1st of July 2021


Sales for Q3-Q4 =

SUMX (

CALCULATETABLE (

tblSales,

DATESYTD ( tblSales[Date] ),

tblSales[Date] = DATE (2021, 06, 31)

),

SUM ( tblSales[Sales] ))

Example

I want to overwrite the start of the year. I want my first date of the year to be 1. February. This measure comes handy if you need to calculate the fiscal year.


_DATESYTD =

CALCULATE (SUM ( tblSales[Sales] ), DATESYTD ( tblSales[Date], "01-31"))





11 views0 comments

Recent Posts

See All

Index

Information functions Filter functions Table manipulation functions Grouping and summarizing Time intelligence functions OPENINGBALANCEYEAR OPENINGBALANCEYEAR(<Expression>,<Dates> [,<Filter>][,<YearEn