Forum Discussion

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

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.

 

5 Replies

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