Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello community,
I have a question
SAMEPERIODLASTYEAR function returns the exact value of the current measure on the context of the filter(year, quarter, month or day)
Now imagine that today = 16/05/2023 and I have a table at year/month level from 2023-01 to 2023-05.
What I want is dax to compute full month comparison based on max date from my dim date.
Basically, if my current max is 16-05-2023, sameperiodlastyear will work great from Jan-Apr, But in May it will compute full month from May-2022
How can I prevent that ?
Solved! Go to Solution.
Hi @Anonymous ,
not sure if i fully get you, but you can add additional date filter argument, like:
SalesLY =
VAR _lastday = MAXX(ALL(data[date]), data[date]) //similar to today()
VAR _result = CALCULATE(
SUM(data[sales]),
SAMEPERIODLASTYEAR(dates[date]),
dates[date]<=EDATE(_lastday, -12)
)
RETURN _result
for a data table like:
date | sales |
1/1/2022 | 1 |
1/21/2022 | 1 |
2/10/2022 | 1 |
3/2/2022 | 1 |
3/22/2022 | 1 |
4/11/2022 | 1 |
5/1/2022 | 1 |
5/21/2022 | 1 |
6/10/2022 | 1 |
6/30/2022 | 1 |
7/20/2022 | 1 |
8/9/2022 | 1 |
8/29/2022 | 1 |
9/18/2022 | 1 |
10/8/2022 | 1 |
10/28/2022 | 1 |
11/17/2022 | 1 |
12/7/2022 | 1 |
12/27/2022 | 1 |
1/16/2023 | 1 |
2/5/2023 | 1 |
2/25/2023 | 1 |
3/17/2023 | 1 |
4/6/2023 | 1 |
4/26/2023 | 1 |
5/16/2023 | 1 |
it worked like:
find more in the attachment.
Hi @Anonymous ,
not sure if i fully get you, but you can add additional date filter argument, like:
SalesLY =
VAR _lastday = MAXX(ALL(data[date]), data[date]) //similar to today()
VAR _result = CALCULATE(
SUM(data[sales]),
SAMEPERIODLASTYEAR(dates[date]),
dates[date]<=EDATE(_lastday, -12)
)
RETURN _result
for a data table like:
date | sales |
1/1/2022 | 1 |
1/21/2022 | 1 |
2/10/2022 | 1 |
3/2/2022 | 1 |
3/22/2022 | 1 |
4/11/2022 | 1 |
5/1/2022 | 1 |
5/21/2022 | 1 |
6/10/2022 | 1 |
6/30/2022 | 1 |
7/20/2022 | 1 |
8/9/2022 | 1 |
8/29/2022 | 1 |
9/18/2022 | 1 |
10/8/2022 | 1 |
10/28/2022 | 1 |
11/17/2022 | 1 |
12/7/2022 | 1 |
12/27/2022 | 1 |
1/16/2023 | 1 |
2/5/2023 | 1 |
2/25/2023 | 1 |
3/17/2023 | 1 |
4/6/2023 | 1 |
4/26/2023 | 1 |
5/16/2023 | 1 |
it worked like:
find more in the attachment.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |