Hello Forum!
I'm writing here with a little problem I can't seem to solve, hoping you can lend me a hand. To put it simply, I have a database with a 'Date' field and a 'Sales' field. What I would like to do is create a visualization like this:
The column "Previous Year Sales" should return the sales for the same timeframe as that row. For example, if I have data for 2024 up to 03/28/2024, it should give me the equivalent sales from 01/01/2023 to 03/28/2024.
To achieve this, I've created this measure, but when I place it in the table, it gives me all the sales from my database for the 2023 row and calculates correctly for 2024. I understand this is due to a problem with the filter context. Is there any way to fix this?
Sales Previous Year =
VAR MaxActualDate = LASTDATE('BBDD'[Date])
VAR MinFechaActual = FIRSTDATE('BBDD'[Date])
VAR PreviousSales =
CALCULATE(
SUM('BBDD'[Sales]),
DATESBETWEEN(
'BBDD'[Date],
DATEADD(MinActualDate, -1, YEAR),
DATEADD(MaxActualDate, -1, YEAR)
),
ALLEXCEPT('BBDD', 'BBDD'[Sales])
)
RETURN
PreviousSales
Hi @Anonymous ,
Thanks for the reply from @danextian , please allow me to provide another insight:
Create measure.
MEASURE =
VAR _min_date =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Year] = MAX ( 'Table'[Year] ) )
)
VAR _max_date =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Year] = MAX ( 'Table'[Year] ) )
)
VAR _pre =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date]
>= DATE ( YEAR ( _min_date ) - 1, MONTH ( _min_date ), DAY ( _min_date ) )
&& 'Table'[Date]
<= DATE ( YEAR ( _max_date ) - 1, MONTH ( _max_date ), DAY ( _max_date ) )
)
)
RETURN
_pre
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Thanks for the reply from @danextian , please allow me to provide another insight:
Create measure.
MEASURE =
VAR _min_date =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Year] = MAX ( 'Table'[Year] ) )
)
VAR _max_date =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Year] = MAX ( 'Table'[Year] ) )
)
VAR _pre =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date]
>= DATE ( YEAR ( _min_date ) - 1, MONTH ( _min_date ), DAY ( _min_date ) )
&& 'Table'[Date]
<= DATE ( YEAR ( _max_date ) - 1, MONTH ( _max_date ), DAY ( _max_date ) )
)
)
RETURN
_preIf your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Danextian!
Thank you for your response. Associating the date with the Date table almost fixed the problem. I've noticed that for certain cases, I'll explain it here as an example and attach a table to make it easier to understand:
For 2024, my database has data from 01/02/2024 to 03/28/2024, and this equates to sales of 140M€. What I would like is for the LYD measure to return the sales for the same period in 2023, which would be 30M€. However, the result I’m getting is 32M€ because it’s taking the period from 01/01/2023 to 03/31/2023.
Taking advantage of this query and very much related to it, if I later want to do the same but with a table like this one, I now want to show the sales for that month in the previous year and another measure that shows the sales for the month immediately preceding the one indicated in the row.
Thank you so much for your help!
Helpful resources
Power BI Monthly Update - November 2025
Check out the November 2025 Power BI update to learn about new features.
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
