Dynamic trailing x days for multiple years in a chart

[tdhlonghorn]

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 chart with Gross Margin & Incremental Landed Margin (I have those calc'd) but I can't envision how to populate my chart when I'm dynamically calculating the sales over the last x number of days.

More about our calendar:

• Current Fiscal Year is 2/1/24 - 1/31/25
• LY would then be 2/2/23 - 2/24 (due to leap day this year)
• essentially, we always want to compare day of week to day of week

I mocked up my desired output in Excel, any help is greatly appreciated!

Excel Mockup 

Additionally, here is a snapshot of what this would look like for trailing 7 days:

[tdhlonghorn · Accepted solution]

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:

[v-jtian-msft]

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.

[tdhlonghorn]

Thank you for looking at this @v-jtian-msft. 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.