cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper II

## Time Intelligence Functions alternative

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 ?

1 ACCEPTED SOLUTION
Super User

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.

Super User

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors