Forum Discussion

Helper I

8 months ago

Calculating Latest Weeks & Comparing to Same Timeframe PY

Hi All, Looking for advice, I'm trying create a filter that has lastest week options for the end user on my dashboard ie. (YTD, L4,L12,L52). I have the following dax code to call the lastest time...

Shubham_rai955

Super User

8 months ago

You can keep your existing base measures and handle the “same timeframe PY” with a time-intelligence wrapper that reuses the same dynamic date range, instead of hard‑coding L4 PY, L12 PY, etc.

Core idea

Your Latest Time Periods table defines a set of dates for the selected period (L4, L12, L52, YTD).
Build measures that:
- First evaluate the base measure over that set of dates.
- Then shift that same set of dates by one year to get the PY value, using DATEADD or SAMEPERIODLASTYEAR.

Example pattern

Assume:

Date table: 'Calendar'[Date] related to Data_Dump_Sheet[Week End].
User selects a period (L4Wks/L12Wks/L52Wks/YTD) from a slicer bound to Latest Time Periods[Period].

1. Current value over selected dynamic period

Measure Current := VAR SelectedDates = CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), KEEPFILTERS ( 'Latest Time Periods' ) ) RETURN CALCULATE ( [Base Measure], -- e.g. Sales, Volume, etc. SelectedDates )

SelectedDates is the exact date set generated by your Latest Time Periods logic.

2. Prior Year over same dynamic window

Measure PY := VAR SelectedDates = CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), KEEPFILTERS ( 'Latest Time Periods' ) ) VAR SelectedDatesPY = DATEADD ( SelectedDates, -1, YEAR ) RETURN CALCULATE ( [Base Measure], SelectedDatesPY )

This shifts the same set of dates back one year, so “L4Wks PY” uses exactly the previous year’s matching weeks, “L12Wks PY” uses the previous 12 weeks, etc., without defining separate measures.

3. Change vs PY

Measure Δ vs PY := [Measure Current] - [Measure PY] Measure % vs PY := DIVIDE ( [Measure Current] - [Measure PY], [Measure PY] )

These measures will respond correctly to any period the user chooses in the slicer (L4, L12, L52, YTD), always comparing to the same timeframe last year using the dynamic window you already built.

L1102
Helper I
8 months ago
HI Shubham_rai955 , this unfortunetly didn't work.
- v-echaithra
  Community Support
  8 months ago
  Hi L1102 ,
  
  Use a proper Calendar table
  
  Relationship:
  
  Calendar[Date] > Data_Dump_Sheet[Week End]
  No relationship from Latest Time Periods > fact table, it must remain disconnected.
  
  Base measure
  [Sales] = SUM ( Data_Dump_Sheet[$ Sales] )
  
  Current Period which is dynamic
  Sales_Selected Period =
  VAR SelectedDates =
  CALCULATETABLE (
  VALUES ( 'Calendar'[Date] ),
  TREATAS (
  VALUES ( 'Latest Time Periods'[Date] ),
  'Calendar'[Date]
  ))
  RETURN
  CALCULATE ( [Sales], SelectedDates )
  
  This applies L4 / L12 / L52 / YTD dynamically.
  
  Prior Year with same timeframe
  
  Sales_Selected Period PY =
  VAR SelectedDates =
  CALCULATETABLE (
  VALUES ( 'Calendar'[Date] ),
  TREATAS (
  VALUES ( 'Latest Time Periods'[Date] ),
  'Calendar'[Date]
  ))
  VAR PYDates =
  DATEADD ( SelectedDates, -1, YEAR )
  RETURN
  CALCULATE ( [Sales], PYDates )
  
  Variance measures:
  
  Sales_1 = [Sales_Selected Period] - [Sales_Selected Period PY]
  
  Sales_%_1 = DIVIDE ( [Sales_1], [Sales_Selected Period PY] )
  
  Hope this helps.
  Thank you.
  - v-echaithra
    Community Support
    8 months ago
    Hi L1102 ,
    
    We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.
    
    Best Regards,
    Chaithra E.