cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lino
Frequent Visitor

Current Year vs Previous Year

Hello everyone

 

I have a table that contains two fields:

 

  • Date
  • Amount

 

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:

 

DateAmountAmount PY
24.03.201614 
24.03.20172314
24.03.20182623

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
CrisYan Resolver II
Resolver II

Re: Current Year vs Previous Year

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!

View solution in original post

7 REPLIES 7
Johanno Resolver III
Resolver III

Re: Current Year vs Previous Year

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.

 

Lino
Frequent Visitor

Re: Current Year vs Previous Year

Hello @Johanno

 

Thank you for the suggestion, but it does not work. 

 

2018-08-31 16_04_42-Test - Power BI Desktop.pngFirst Case

2018-08-31 16_09_17-Test - Power BI Desktop.pngSecond Case

 

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

Johanno Resolver III
Resolver III

Re: Current Year vs Previous Year

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.
Highlighted
CrisYan Resolver II
Resolver II

Re: Current Year vs Previous Year

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!

View solution in original post

Lino
Frequent Visitor

Re: Current Year vs Previous Year


@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.

Lino
Frequent Visitor

Re: Current Year vs Previous Year


@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

Anonymous
Not applicable

Re: Current Year vs Previous Year


Hi,

I have a list of 5 State Financial years ranging from 2013 to 2018. I am able to calculate difference between two consecutive years(2017,2018) and difference between current year 2018 with any previous year but not able to calculate difference for previous years like (2015,2017) or (2016, 2014)

The DAX functions I used here are: YTD= Total YTD([Value], (Date), “6/30”)
PYTD = Calculate([YTD], Datesbetween(Date), Date(2013,7,1), Date(2017,6,30)))

Can someone assist me here? Thanks!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors