Forum Discussion
Using Date Table Offsets Instead of Time Intelligence Functions
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_SalesModify this as per your need.
Did I solve your query ? Mark this as solution , appreciate Kudos always.
Cheers
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
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