Forum Discussion

timosborn's avatar
timosborn
Frequent Visitor
1 year ago
Solved

Using Date Table Offsets Instead of Time Intelligence Functions

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...
General Comment
Need Help
  • divyed's avatar
    divyed
    1 year ago

    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

divyed's avatar
divyed
Super User
1 year ago

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's avatar
    timosborn
    Frequent Visitor
    1 year ago

    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