DAX Time Intelligence Functions - Performance Data Calculation

0
votes

I'm trying to use the DAX Time Intelligence Functions in a Tabular Model in Analysis Services in Visual Studio 2017.

I have the following calculation requirements:

  1. Date (DateTime)
  2. Monthly Performance (in percentage)
  3. Account

For each account, we would like to calculate the YTD, 1Y, 2Y, 3M, 5M Performance metrics...

using the following performance formula:

Perf for 3M =
(1 + CurrentMonthPerf/100)*(1 + PriorMonth/100)*(1 + PriorPriorMonth/100) - 1

Perf for 1Y =
(1 + CurrentMonthPerf/100)*(1 + {1MonthAgoPerf}/100)*...* (1 + {12MonthsAgoPerf}/100) - 1

Perf for YTD = Same calculation as above but only for the months from Jan till Current table cell. Each row will calculate the Product in a loop: (1 + X)...(1 + Y)) - 1

I'm new to DAX syntax so unsure how to perform this calculation using DAX in SSDT 2017.

Please advise how I can perform more complex date calculations.

1
sql-servervisual-studiossasdaxsql-server-data-tools

1 Answers

2
votes

I've written my monthly linking of returns in my own reports roughly like this (assuming I have a monthly time series of returns):

Performance = 
VAR StartDate = <...>
VAR EndDate = <...>
RETURN -1 +
PRODUCTX(
    FILTER( ALL( Table1[Date] ),
        Table1[Date] <= EndDate &&
        Table1[Date] > StartDate
    ),
    1 + Table1[MonthReturn]
)

Basically, you use a product on a filtered table instead of writing a loop.

This is a highly simplified form where I've left the definition of the start and ending date undefined and assuming you only have one return value per month, but it should help point you in the right direction.