Calculating Latest Weeks & Comparing to Same Timeframe PY

[L1102]

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 timeframes, but my measures (Prior Year, change measures etc.) don't calculate properly becuase it's not evaluating against the same lastest timeframe LY ie. L4 vs L4 PY. Is this possible to do in power BI without needing to create all new measures?

Latest Time Periods =

VAR _Today = MAX(Data_Dump_Sheet[Week End])

VAR _ThisYear = YEAR(_Today)

VAR _ThisMonth = MONTH(_Today)

VAR _ThisDay = DAY(_Today)

RETURN

    SELECTCOLUMNS(

        UNION

        (

            // L52Wks

            ADDCOLUMNS(

            GENERATE(

                SELECTCOLUMNS({"L52Wks"},"Period",[Value]),

                GENERATESERIES(

                    DATE(_ThisYear,_ThisMonth, _ThisDay -364),

                    _Today-1

                )

            ),"Axis Date",[Value]),

            // L12Wks

            ADDCOLUMNS(

            GENERATE(

                SELECTCOLUMNS({"L12Wks"},"Period",[Value]),

                GENERATESERIES(

                    DATE(_ThisYear,_ThisMonth, _ThisDay -84),

                    _Today-1

                )

            ),"Axis Date",[Value]),

            // L4Wks

            ADDCOLUMNS(

            GENERATE(

                SELECTCOLUMNS({"L4Wks"},"Period",[Value]),

                GENERATESERIES(

                    DATE(_ThisYear,_ThisMonth, _ThisDay -28),

                    _Today-1

                )

            ),"Axis Date",[Value]

            )

,

            // YTD

                GENERATE(

                SELECTCOLUMNS({"YTD"},"Period",[Value]) ,

                VAR BaseTable =

                    SELECTCOLUMNS(

                        GENERATESERIES(

                            DATE(_ThisYear, _ThisMonth -5, 1) ,

                            _Today

                            ),"D1",[Value]

                    )

                RETURN

                    SELECTCOLUMNS(

                        GENERATE(

                            BaseTable ,

                            FILTER(

                                SELECTCOLUMNS(BaseTable,"D2",[D1]) ,[D2]<=EARLIER([D1]))

                            )

                        ,"Date",[D2]

                        ,"Axis Date",[D1]

                    )

                )

            ) ,  

        "Period" , [Period] ,

        "Date" , [Value] ,

        "Axis Date" , [Axis Date]

    )

[jucarami]

Fechas Duplicadas y Relación Muchos-a-Muchos Tu tabla 'Últimos períodos de tiempo' genera fechas superpuestas. Por ejemplo, el día de hoy existe tanto en el grupo "L4" como en "L12" y "YTD". Esto obliga a que la relación entre tu nueva tabla y tu tabla de Calendario (Dates) sea de Muchos a Muchos (Many-to-Many). Las funciones de inteligencia de tiempo estándar (SAMEPERIODLASTYEAR, DATEADD) a menudo fallan o dan resultados impredecibles bajo relaciones Muchos-a-Muchos porque pierden la continuidad del contexto de filtro de la fecha.

2.  Grupos de Cálculo Si no quieres reescribir tus medidas (Ventas, Costos, Margen, etc.) una por una, la única y mejor forma de hacerlo es usando Calculation Groups (Grupos de Cálculo).

Esto te permite inyectar la lógica de tiempo a cualquier medida que arrastres al gráfico.

Pasos para solucionarlo:

Mantén tu tabla: Deja tu tabla de Últimos períodos de tiempo tal como está, pero asegúrate de que la relación con tu tabla de Calendario esté activa (probablemente será bidireccional o M:M, lo cual está bien si usamos Grupos de Cálculo, pero idealmente debería ser unidireccional filtrando la tabla de Fechas).

Crea un Grupo de Cálculo: (Necesitas Tabular Editor, que es una herramienta externa gratuita que se conecta a Power BI, o usar la vista de modelo si tienes la versión más reciente de PBI).

Crea un Calculation Group llamado "Inteligencia de Tiempo".

Crea un Calculation Item llamado "Actual" con la fórmula: SELECTEDMEASURE()

Crea un Calculation Item llamado "Año Anterior" con la fórmula:

Fragmento de código

CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR( 'TuTablaCalendario'[Date] )
)
¿Por qué esto funciona? Al usar el Grupo de Cálculo, Power BI primero filtra las fechas según tu selección (L4, L12, etc.) y luego, al aplicar el ítem "Año Anterior", toma ese contexto de fechas ya filtrado y lo desplaza un año atrás sobre la medida original.

[Shubham_rai955]

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]

HI @Shubham_rai955 , this unfortunetly didn't work.

[danextian]

I use AI myself but whenever posting an AI-generated response as a solution, it should always be validated. KEEPFILTERS require a column and a filter criterion. At first glance  KEEPFILTERS(Latest_Time_Periods) is already sytactically wrong and @L1102 has validated that is indeed sytactically  wrong by testing.

[d_m_LNK]

Instead of hard coding the year you should reference your date table to determine the current year otherwise you will need to change this every time.  For you time frame selections, is there a relationship between your Latest time periods table and your sales table?

[L1102]

@d_m_LNK , I hard code the year because my fiscal calendar starts in May and is currently fiscal year 2026, however when I import my data from it's source it has the proper calendar date. For example, my fiscal year has a date range of May 2025 - May 2026. When I calculate YOY changes I needed it to identify what year is current and what was prior year because there is a combination of both 2025 & 2026 in one fiscal year.

I've only been doing dax and power bi since september so i'm still very green. If you know a better way to evaluate my years I'm open to all suggestions.

[L1102]

Actually I found a way not to hard code the year... thanks for the tip!!

$ Sales CY =

VAR Current_Year = MAX(Promo Calendar'[Year])

RETURN

CALCULATE(sum(Data_Dump_Sheet[$ Sales]), Promo Calendar'[Year] = Current_Year)

[v-echaithra]

Hi @L1102 ,

Thank you for sharing the update and confirming that issue has been resolved. 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]

Hello @v-echaithra , my issue wasn't resolved. d_m_LNK suggested a change in dax on a different measure I had. I still don't have a solution to my original problem.

[L1102]

Hi @Ahmed-Elfeel , what is the KEEPFILTERS( Latest_Time_Period referring too?) I'm not sure what to link this field too? Also getting 

[Natalie_iTalent]

Hello @L1102,

The response you are referring to above has been removed for being inaccurate.

Best,

Natalie H.

Community Manager 

[amitchandak]

@L1102 , You need to have the week Number or year week column in the date table. 
These will help in grouping 

Week Number = WEEKNUM([Date],2)

WeekDay = weekday([Date],2) // Monday Start user 1 for Sunday Start 

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

For WOW 
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

Measures 
week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[L1102]

Hi @amitchandak ,

I'm not sure if grouping in this way is going to help. Right now I have the latest weeks being called with the DAX I have, but my measures that are caluclating CY & PY are by filtering on the year. I have a feeling this is where I'm getting stuck with my measure calculations. Looking to see if there is a work around for this, or do I need to create all new measures? ideally the user has the ability to choose if they want to select specific weeks in a seperate slicer or use the latest timeframes slicer depicted below. I'm just not sure how to calculate the current year and prior year to allow for this, or even if it's possible.

I've also created this calendar that i'm trying to use for time intelligence

[FBergamaschi]

Can you please clarify

- what L4,L12,L52 mean

- how you are using this code (in a filter for a measure to use in a visual? If yes, what columns are you grouping in Rows and what is the code of the measure? Or is this a calculated table?)

- what is the data model you have, please show and comment the tables

In addition, please show an image of the result you are currently getting and what you are looking for as good result

Thanks

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[L1102]

Hi @FBergamaschi  , I'm trying have it available for the end user as a slicer, so instead of going in and clicking all the weeks they want to evaluate, insteadt they have the options to choose to evaluate YTD, L4, L52 etc. The end goal is once they choose L4, all my charts and measures calculate/update visual to L4 weeks vs L4 week same timeframe last year.

Right now I have the latest weeks being called with the DAX I have, but my measures that are caluclating CY & PY are by filtering on the year. I have a feeling this is where I'm getting stuck with my measure calculations. Looking to see if there is a work around for this, or do i need to create all new measures for this?

[d_m_LNK]

Are you applying this measure as a filter of a visual right now or how are you trying to apply this code?  It looks like you could create this as a calculated table within your model to then use with visuals and measures but you would need to build relationships to your fact with this.  You would also need to reference these dates in your measures as I don't know of a way to get around that.  

[L1102]

Hi @d_m_LNK , I'm trying have it available for the end user as a slicer, so instead of going in and clicking all the weeks they want to evaluate, insteadt they have the options to choose to evaluate YTD, L4, L52 etc. The end goal is once they choose L4, all my charts and measures calculate/update visual to L4 weeks vs L4 week same timeframe last year.

Right now I have the latest weeks being called with the DAX I have, but my measure that are caluclating CY & PY are by filtering on the year. I have a feeling this is where I'm getting stuck with my measure calculations. Looking to see if there is a work around for this, or do i need to create all new measures for this?