CODEWITHSUNDEEP
excelpowerbidaxbusiness-intelligencepowerpivot
David542
David542

Reputation: 110382

Time intelligence in BI tools

In a language such as DAX, there is an entire page of functions related to (more-or-less) creating date tables of various start/end dates. For example:

DATESMTD Returns a table that contains a column of the dates for the month to date, in the current context.

What are the main uses of this?

Upvotes: 0

Views: 126

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Suppose you want to create a slicer where you select a date and want to show the month-to-date, quarter-to-date, and year-to-date totals as separate metrics.

Instead of having to write the logic for these period-to-date metrics by hand, you can simply write:

TotalMTD = CALCULATE ( [Total], DATESMTD ( 'Date'[Date] )
TotalQTD = CALCULATE ( [Total], DATESQTD ( 'Date'[Date] )
TotalYTD = CALCULATE ( [Total], DATESYTD ( 'Date'[Date] )

Instead of having to write something more like this:

TotalQTD =
VAR SelectedDate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR QuarterStart =
    EOMONTH ( SelectedDate, - MOD ( MONTH ( SelectedDate ) - 1, 3 ) - 1 )
RETURN
    CALCULATE (
        [Total],
        'Date'[Date] <= SelectedDate,
        'Date'[Date] > QuarterStart
)

For many more examples of time intelligence uses see here:

https://www.daxpatterns.com/standard-time-related-calculations/

Upvotes: 2

Related Questions