Forum Discussion
Helper ICalculating 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 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?
21 Replies
- danextian
Super User
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.
- amitchandak
L1102 , You need to have the week Number or year week column in the date table.
These will help in groupingWeek 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.
L1102Hi 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
- 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])RETURNCALCULATE(sum(Data_Dump_Sheet[$ Sales]), Promo Calendar'[Year] = Current_Year)
- 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?
- 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?
- 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
Administrator
Hello L1102,
The response you are referring to above has been removed for being inaccurate.
Best,
Natalie H.
Community Manager
Shubham_rai955You 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
- v-echaithra
Community Support
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.
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.
