Time Intelligence Functions alternative

[Anonymous]

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 ?

[FreemanZ · Accepted 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.