Forum Discussion

tdhlonghorn's avatar
tdhlonghorn
Helper I
2 years ago
Solved

Dynamic 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 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:

 

Need Help
  • tdhlonghorn's avatar
    tdhlonghorn
    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:

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      tdhlonghorn
      Helper I
      2 years ago

      Thank 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.

  • tdhlonghorn's avatar
    tdhlonghorn
    Helper I
    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: