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

SamWiseOwl's avatar
SamWiseOwl
Super User
1 year ago

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
)