Forum Discussion
Helper IDynamic trailing x days for multiple years in a chart
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:
Hello,tdhlonghorn .
I am glad to help you.
According to your description, you want to dynamically calculate and display the sales for the last X days in the icon, right?
You can use WeekNum = WEEKNUM('Table'[Dates],13)
Setting the parameter to 13 corresponds perfectly to the number of weeks in your data.
According to the data you gave us, we compared the weekly data of 21 weeks in the last three years.
Here are my test results:
WeekNum = WEEKNUM('Table'[Dates],13)Week_CrossMargin =
VAR WeekNum_ =[WeekNum]
VAR YearNum_ =YEAR('Table'[Dates])
var result=
CALCULATE(SUM('Table'[Gross Margin]),FILTER(ALL('Table'),'Table'[WeekNum]=WeekNum_ &&YEAR('Table'[Dates])=YearNum_))
RETURN result
Week_Incremental Landed Margin =
VAR WeekNum_ =[WeekNum]
VAR YearNum_ =YEAR('Table'[Dates])
var result=
CALCULATE(SUM('Table'[Incremental]),FILTER(ALL('Table'),'Table'[WeekNum]=WeekNum_ &&YEAR('Table'[Dates])=YearNum_))
RETURN result
Week_Revenue =
VAR WeekNum_ =[WeekNum]
VAR YearNum_ =YEAR('Table'[Dates])
var result=
CALCULATE(SUM('Table'[Revenue]),FILTER(ALL('Table'),'Table'[WeekNum]=WeekNum_ &&YEAR('Table'[Dates])=YearNum_))
RETURN result
GM% =
DIVIDE('Table'[Week_CrossMargin],'Table'[Week_Revenue])
Incr Landed % =
DIVIDE('Table'[Week_Incremental Landed Margin],'Table'[Week_Revenue])
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IThank you for looking at this Anonymous. While this solution works, unfortunately it doesn't meet my needs as
- the dynamic timeframes are not week-based, but rather trailing 7, trailing 14, etc.
- I believe WEEKNUM has trouble with a 2/1 fiscal year start
In any case - I think I've got it worked out by adding columns to my date table, and using SWITCH depending on the timeframe selected.