Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 (
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
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 (
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
Dear @nandic ,
Thank you very much for the suggested solution. I'll try it in my model and see if it works.
Cheers!
Vladimir
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |