cancel
Showing results for
Search instead for
Did you mean:
New Member

## SAMEPERIODLASTYEAR() function comparison

Dear Community,

I would like to compare sales results in the same period of two consecutive years by using the SAMEPERIODLASTYEAR() function. When I do that, the presented result is not what I expected.

Instead of comparing sales results in the same period (e.g. January-June 2021. and 2020.), I'm getting the sales result for January-June 2021. and comparing it with the sales results of the complete 2020. (e.g. January-December 2020).
I'm using the function in format: IF(ISBLANK([Value]),BLANK(),CALCULATE([Value],SAMEPERIODLASTYEAR(Calendar[Date])))

SQL server is the source of data and I'm using the data by importing them to the PBI desktop. Also, I'm using the Calendar table in my model.

I appreciate any help in advance!

Vladimir

1 ACCEPTED SOLUTION
Super User II

Hi @Vladimir_P ,

You might try this formula:

PY amount =
VAR _year =
MAX ( Date[Year] )
VAR _year_month =
SELECTCOLUMNS (
FILTER (
SUMMARIZE ( Fact, Fact[Month], Fact[Year] ),
Fact[Year] = _year
),
"YM", Fact[Month]
)
RETURN
CALCULATE (

SUM(Fact[Amount])SAMEPERIODLASTYEAR ( Date[Date] ), _year_month )

If it doesn't work could you please share screenshot of your model and DAX that you used?
This formula is based on 1 Fact table and 1 date table.

Cheers,
Nemanja Andic

2 REPLIES 2
Super User II

Hi @Vladimir_P ,

You might try this formula:

PY amount =
VAR _year =
MAX ( Date[Year] )
VAR _year_month =
SELECTCOLUMNS (
FILTER (
SUMMARIZE ( Fact, Fact[Month], Fact[Year] ),
Fact[Year] = _year
),
"YM", Fact[Month]
)
RETURN
CALCULATE (

SUM(Fact[Amount])SAMEPERIODLASTYEAR ( Date[Date] ), _year_month )

If it doesn't work could you please share screenshot of your model and DAX that you used?
This formula is based on 1 Fact table and 1 date table.

Cheers,
Nemanja Andic

New Member

Dear @nandic ,

Thank you very much for the suggested solution. I'll try it in my model and see if it works.
Cheers!
Vladimir

## Helpful resources

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

#### Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors