Time Intelligence Functions alternative

Anonymous — Thu, 30 Nov 2023 22:59:44 GMT

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 ?

Re: Time Intelligence Functions alternative

FreemanZ — Fri, 01 Dec 2023 01:30:12 GMT

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.

topic Time Intelligence Functions alternative in DAX Commands and Tips

Time Intelligence Functions alternative

Re: Time Intelligence Functions alternative