Forum Discussion

Lino's avatar
Lino
Frequent Visitor
8 years ago
Solved

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 ...
Need Help
  • CrisYan's avatar
    CrisYan
    8 years ago

    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!

Johanno's avatar
Johanno
Icon for Continued Contributor rankContinued Contributor
8 years ago

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's avatar
Lino
Frequent Visitor
8 years ago

Hello Johanno

 

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

 

First Case

Second 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

  • CrisYan's avatar
    CrisYan
    Icon for Resolver III rankResolver III
    8 years ago

    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!

    • Lino's avatar
      Lino
      Frequent Visitor
      8 years ago
      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's avatar
    Johanno
    Icon for Continued Contributor rankContinued Contributor
    8 years ago
    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.
    • Lino's avatar
      Lino
      Frequent Visitor
      8 years ago
      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.