cancel
Showing results 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!

1 ACCEPTED SOLUTION  Super User II

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

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! Announcements #### 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  