Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |