topic Re: Calculating Latest Weeks & Comparing to Same Timeframe PY in DAX Commands and Tips

Calculating Latest Weeks & Comparing to Same Timeframe PY

L1102 — Fri, 12 Dec 2025 18:49:16 GMT

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]

)

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

d_m_LNK — Fri, 12 Dec 2025 21:34:23 GMT

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.

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

FBergamaschi — Sat, 13 Dec 2025 10:12:06 GMT

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

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

amitchandak — Mon, 15 Dec 2025 12:05:55 GMT

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

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

L1102 — Wed, 17 Dec 2025 15:58:18 GMT

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

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

L1102 — Wed, 17 Dec 2025 16:11:42 GMT

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?

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

L1102 — Wed, 17 Dec 2025 16:14:36 GMT

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?

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

d_m_LNK — Wed, 17 Dec 2025 16:28:55 GMT

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?

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

L1102 — Wed, 17 Dec 2025 16:37:51 GMT

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

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

L1102 — Wed, 17 Dec 2025 18:25:18 GMT

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.

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

L1102 — Wed, 17 Dec 2025 18:44:35 GMT

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)

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

v-echaithra — Thu, 18 Dec 2025 06:36:52 GMT

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.

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

L1102 — Thu, 18 Dec 2025 16:32:16 GMT

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.

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

danextian — Fri, 19 Dec 2025 05:04:40 GMT

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.

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

Shubham_rai955 — Fri, 19 Dec 2025 11:02:38 GMT

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.

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

jucarami — Fri, 19 Dec 2025 18:34:21 GMT

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.

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

Natalie_iTalent — Fri, 19 Dec 2025 22:26:51 GMT

Hello L1102,

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

Best,

Natalie H.

Community Manager

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

L1102 — Tue, 23 Dec 2025 07:31:52 GMT

HI Shubham_rai955 , this unfortunetly didn't work.

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

v-echaithra — Wed, 24 Dec 2025 09:52:32 GMT

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.

Re: Calculating Latest Weeks & Comparing to Same Timeframe PY

v-echaithra — Tue, 30 Dec 2025 11:14:34 GMT

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.