Using Date Table Offsets Instead of Time Intelligence Functions

[timosborn]

I've been tryign to limit my use of time intelligence functions and also calculate as I find they are generally better way of coding for various reasons.

One of the things I have found is that using offsets to do something like return sales for Last Year with the Financial Year Offset as - 1, is incompatible with a month granularity.

For example, we have the following measure.

Revenue LY NO CALCULATE FYear Offset =
VAR __sel_offset =

SELECTEDVALUE(Dates[FYearOffset])


VAR __sel_prev_offset =

__sel_offset -1


VAR __vTablePrev =

FILTER(
ALL(Dates),
Dates[FYearOffset] = __sel_prev_offset
)


VAR __LY_Sales =

SUMX(
__vTablePrev,
[Revenue]
)


RETURN

__LY_Sales

This works in a table with financial years but if I change the granularity of the table to month & year, the whole financial year amount is returned (example attached).

Because of this issue, I still find myself using DATEADD for LY calculations:

Revenue LY DATEADD =
VAR __LY =

CALCULATE(
[Revenue],
DATEADD(Dates[Date],-1,YEAR)
)

RETURN

__LY

As it appears to be the most flexible and easy to use.  However I realise the drawback in using TI functions as they are "black box" features that don't really let you see what is going on inside of them.

Does anyone know a way to solve for this while retaining the use of offsets?

I have worked out a way to do it using ISINSCOPE to see if we are on the month or year level in the table, but this method is quite verbose and appears unessesarily complex.

[divyed · Accepted solution]

Hello @timosborn ,

Not sure about exact requirement but if you want your calculation to work for different hierarchy without calculate and TL, try using below dax :

Revenue LY NO CALCULATE FYear Offset =
VAR __sel_offset = SELECTEDVALUE(Dates[FYearOffset])
VAR __sel_prev_offset = __sel_offset - 1

// virtual table for the previous period
VAR __vTablePrev =
FILTER(
ALL(Dates),
Dates[FYearOffset] = __sel_prev_offset
)

// Apply the selected date context dynamically with TREATAS for various hierarchies
VAR __LY_Sales =
SUMX(
TREATAS(__vTablePrev, Dates[Year], Dates[Month], Dates[Day]), // Modify this as per your requirement
[Revenue]
)

RETURN
__LY_Sales

Modify this as per your need.

Did I solve your query ? Mark this as solution , appreciate Kudos always.

Cheers

[timosborn]

thanks divyed that worked, now all I need to do is fix the table totals.  It wouldn't be PBI without an incorrect table total 😂

My end code:

VAR __sel_offset = 
    
    SELECTEDVALUE(Dates[FYearOffset])


VAR __sel_prev_offset = 
    
    __sel_offset -1



VAR __vTablePrev =
    
    FILTER(
        ALL(Dates[FYear],Dates[FYearOffset],Dates[Month], Dates[Period]),
        Dates[FYearOffset] = __sel_prev_offset 
    )


VAR __LY_Sales =
    
    SUMX(
        TREATAS(
            __vTablePrev,Dates[fyear],Dates[FYearOffset],Dates[Month],Dates[period]
        ),
        [Revenue]
    )


RETURN

    __LY_Sales

 

[SamWiseOwl]

Hi @timosborn 

Do you have a seperate Month column, if so you could apply that as a secondary filter.

Var _vMonth = SelectedValue(Dates[Month])

VAR __vTablePrev =

FILTER(
ALL(Dates),
Dates[FYearOffset] = __sel_prev_offset && Dates[Month] =_vMonth
)