Forum Discussion
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
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
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 AndicDear nandic ,
Thank you very much for the suggested solution. I'll try it in my model and see if it works.
Cheers!
Vladimir
