Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 @carlovsky ,
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 @carlovsky ,
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 |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |