Forum Discussion
tdhlonghorn
Helper I
2 years agoDynamic trailing x days for multiple years in a chart
Hello, I've been struggling with the best method of displaying data across multiple years for the same metric due to our irregular corporate calendar. My desired output would be a stacked column ...
- 2 years ago
I was able to achieve the desired result by adding columns for each dynamic timeframe indicating whether or not a date should be included in the output:
Yesterday = VAR Yday = MIN(TODAY() - 1, MAX(DatesYoY[TY Date])) VAR LYOffset = LOOKUPVALUE(YearAdjust[Days Offset], YearAdjust[Year], "LY Adjust") VAR LLYOffset = LOOKUPVALUE(YearAdjust[Days Offset], YearAdjust[Year], "LLY Adjust") RETURN IF( Calendar_Lookup[FullDateAlternateKey] = Yday || Calendar_Lookup[FullDateAlternateKey] = Yday - LYOffset || Calendar_Lookup[FullDateAlternateKey] = Yday - LLYOffset, 1, 0 )T-7 = VAR Yday = MIN(TODAY() - 1, MAX(DatesYoY[TY Date])) VAR DayOffset = LOOKUPVALUE(Timeframes[Offset], Timeframes[Date Ranges], "Trailing 7") VAR LYOffset = LOOKUPVALUE(YearAdjust[Days Offset], YearAdjust[Year], "LY Adjust") VAR LLYOffset = LOOKUPVALUE(YearAdjust[Days Offset], YearAdjust[Year], "LLY Adjust") VAR TYStart = Yday - DayOffset VAR LYStart = TYStart - LYOffset VAR LYEnd = Yday - LYOffset VAR LLYStart = TYStart - LLYOffset VAR LLYEnd = Yday - LLYOffset RETURN IF( (Calendar_Lookup[FullDateAlternateKey] >= TYStart && Calendar_Lookup[FullDateAlternateKey] <= Yday) || (Calendar_Lookup[FullDateAlternateKey] >= LYStart && Calendar_Lookup[FullDateAlternateKey] <= LYEnd) || (Calendar_Lookup[FullDateAlternateKey] >= LLYStart && Calendar_Lookup[FullDateAlternateKey] <= LLYEnd), 1, 0 )and so on for each time frame. I then wrote a measure for each individual metric that needs to be summed (Margin $ as an example):
FILTERED Dynamic Time Margin $ = SWITCH(SELECTEDVALUE(Timeframes[TimeframeIndex]), 1, CALCULATE([Margin $], Calendar_Lookup[Yesterday] = 1), 2, CALCULATE([Margin $], Calendar_Lookup[T-7] = 1), 3, CALCULATE([Margin $], Calendar_Lookup[T-14] = 1), 4, CALCULATE([Margin $], Calendar_Lookup[T-30] = 1), 9, CALCULATE([Margin $], Calendar_Lookup[YTD] = 1), 10, CALCULATE([Margin $], Calendar_Lookup[STD] = 1) )which allowed me to calculate the margin rates by year and get my desired output:
tdhlonghorn
Helper I
2 years agoI was able to achieve the desired result by adding columns for each dynamic timeframe indicating whether or not a date should be included in the output:
Yesterday =
VAR Yday = MIN(TODAY() - 1, MAX(DatesYoY[TY Date]))
VAR LYOffset = LOOKUPVALUE(YearAdjust[Days Offset], YearAdjust[Year], "LY Adjust")
VAR LLYOffset = LOOKUPVALUE(YearAdjust[Days Offset], YearAdjust[Year], "LLY Adjust")
RETURN
IF(
Calendar_Lookup[FullDateAlternateKey] = Yday ||
Calendar_Lookup[FullDateAlternateKey] = Yday - LYOffset ||
Calendar_Lookup[FullDateAlternateKey] = Yday - LLYOffset,
1,
0
)T-7 =
VAR Yday = MIN(TODAY() - 1, MAX(DatesYoY[TY Date]))
VAR DayOffset = LOOKUPVALUE(Timeframes[Offset], Timeframes[Date Ranges], "Trailing 7")
VAR LYOffset = LOOKUPVALUE(YearAdjust[Days Offset], YearAdjust[Year], "LY Adjust")
VAR LLYOffset = LOOKUPVALUE(YearAdjust[Days Offset], YearAdjust[Year], "LLY Adjust")
VAR TYStart = Yday - DayOffset
VAR LYStart = TYStart - LYOffset
VAR LYEnd = Yday - LYOffset
VAR LLYStart = TYStart - LLYOffset
VAR LLYEnd = Yday - LLYOffset
RETURN
IF(
(Calendar_Lookup[FullDateAlternateKey] >= TYStart && Calendar_Lookup[FullDateAlternateKey] <= Yday) ||
(Calendar_Lookup[FullDateAlternateKey] >= LYStart && Calendar_Lookup[FullDateAlternateKey] <= LYEnd) ||
(Calendar_Lookup[FullDateAlternateKey] >= LLYStart && Calendar_Lookup[FullDateAlternateKey] <= LLYEnd),
1,
0
)and so on for each time frame. I then wrote a measure for each individual metric that needs to be summed (Margin $ as an example):
FILTERED Dynamic Time Margin $ = SWITCH(SELECTEDVALUE(Timeframes[TimeframeIndex]),
1, CALCULATE([Margin $], Calendar_Lookup[Yesterday] = 1),
2, CALCULATE([Margin $], Calendar_Lookup[T-7] = 1),
3, CALCULATE([Margin $], Calendar_Lookup[T-14] = 1),
4, CALCULATE([Margin $], Calendar_Lookup[T-30] = 1),
9, CALCULATE([Margin $], Calendar_Lookup[YTD] = 1),
10, CALCULATE([Margin $], Calendar_Lookup[STD] = 1)
)which allowed me to calculate the margin rates by year and get my desired output: