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.
Hello everyone
I have a table that contains two fields:
This table contains data of 2016, 2017 and 2018.
My scope is creating a measure called Amount PY using DAX. For each record I want to be able to see the amount of the previous year based on the record date.
For example, taking the following records:
Date | Amount | Amount PY |
24.03.2016 | 14 | |
24.03.2017 | 23 | 14 |
24.03.2018 | 26 | 23 |
I found a workaround using Power Query, but I do not like it. I would like to find a way just importing a single table and calculate the Amount PY using DAX via custom measure.
Is there someone who knows how to do it?
Thanks
Lino
Solved! Go to Solution.
You must use SAMEPERIODLASTYEAR() instead of PREVIOUSUEAR(), because the last one returns ALL dates from the last year, and you need the same day shifted one year back (Using SAMEPERIODLASTYEAR()).
Regards!
Hi, try:
PY = CALCULATE(SUM('Table1'[Amount]);PREVIOUSYEAR(Date[Date]))
After testing I realize you first need a separate date table for this to work, e.g. New Table: Date = CALENDAR(DATE(2016;01;01);DATE(2018;12;31)). Then you can use the DAX above.
Hello @Johanno
Thank you for the suggestion, but it does not work.
In the First Case no value is showed, while in the Second Case it returns the total of the previous year basing on the year of the Date field.
For each day of the Date field, it should return the Amount of the same day in the previous year.
Regards
Lino
You must use SAMEPERIODLASTYEAR() instead of PREVIOUSUEAR(), because the last one returns ALL dates from the last year, and you need the same day shifted one year back (Using SAMEPERIODLASTYEAR()).
Regards!
@CrisYan wrote:You must use SAMEPERIODLASTYEAR() instead of PREVIOUSUEAR(), because the last one returns ALL dates from the last year, and you need the same day shifted one year back (Using SAMEPERIODLASTYEAR()).
Regards!
I tried more and more times this function and it never worked. Now, after your suggestion, I tried another time and now it works.
Thanks a lot for the suggestion.
Regards
Lino
@Johanno wrote:
I think you need a separate date table for this to work, I can only see one table. Then you have to create a relationship between the two date columns.
Yes, this is the workaround that I already implemented, and it works, but I am loooking for a way using a single table.
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 |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |