Forum Discussion

L1102's avatar
L1102
Icon for Helper I rankHelper 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's avatar
Shubham_rai955
Icon for Super User rankSuper 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's avatar
L1102
Icon for Helper I rankHelper I
8 months ago

HI Shubham_rai955 , this unfortunetly didn't work.

 

 

 

  • v-echaithra's avatar
    v-echaithra
    Icon for Community Support rankCommunity 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's avatar
      v-echaithra
      Icon for Community Support rankCommunity 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.

    • L1102's avatar
      L1102
      Icon for Helper I rankHelper I
      8 months ago

      Hi v-echaithra ,

       

      A couple of questions...

       

      1. A. Is this dax measure specific, and need to use this dax for all measures in the model?

      Reason I ask is because I have many measures and was hoping not to need to create all new ones. I also have multiple data tables rolling into the model.

      B. Would this work with as a calculated group using syntax "SelectedMeasure"?

       

      2. If I disconnect my relationship between Lastest Time Period & Fiscal Calendar, is there another way to get the L4, L12, L52, YTD visual below for the end user?

       

       

      Let me know your thoughts.

       

      Thank you.

      Laura

       

      • v-echaithra's avatar
        v-echaithra
        Icon for Community Support rankCommunity Support
        7 months ago

        Hi L1102 ,

        You do NOT need to recreate all of your business measures.
        Your existing measures like [Sales], [Units], [Margin], etc. should remain exactly as they are.
        The key is that the logic for L4 / L12 / L52 / YTD and Same Timeframe PY should not live inside each base measure. That logic belongs in a time-filtering layer that wraps those measures.

        Yes, the best and most scalable solution is to implement this using a Calculation Group with SELECTEDMEASURE().

        This gives you no measure duplication, works automatically across all existing and future measures and works with multiple fact tables.

        Keeping Latest Time Periods disconnected is correct and required. The slicer still works because the slicer filters the Latest Time Periods table, TREATAS() explicitly maps those selected dates onto Calendar[Date]. The Calendar table then filters the fact tables through normal relationships which allows you to use one slicer for L4 / L12 / L52 / YTD.

        Please provide the sample Pbix file. How to provide sample data in the Power BI Forum

        You can refer the following link to upload the file to the community.
        How to upload PBI in Community

        We are available to support you and are committed to helping you reach a resolution.

        Best Regards,
        Chaithra E.