Forum Discussion

GMS0101's avatar
GMS0101
Frequent Visitor
7 years ago
Solved

SAMEPERIODLASTYEAR only calculating cumulative amounts

Hello!

I've seen many forum pages and blogs of people using a combinatio of CALCULATE and SAMEPERIODLASTYEAR to calculate prior period revenue but my formulas to get prior period revenue return the cumulative sum, one period back. In this sample case, I'm trying to calculate prior year revenue.

 

My Measures, report, and table information are below:

Prior Period Sales = CALCULATE(sum(MyTable[Sales]),DATEADD(MyTable[Date],-1,YEAR))

Prior Period Sales 2 = CALCULATE([Total Sales],SAMEPERIODLASTYEAR(MyTable[Date]))

 

 

 

I'm expecting to see the prior year's revenue on each line but I get blanks. The subtotal for each of the Prior year columns is a cumulative sum of all years up to the prior year. How do I get prior year revenue to show up on those blank spots?

 

Thank you to anyone who can help this PowerBI noob with his troubles.

 

Garrett

 

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    Hi GMS0101 ,

    You need to create a new calendar table and create a relationship between the two tables.

    Table = CALENDARAUTO()

    Prior Period Sales   = CALCULATE(sum(MyTable[Sales]),DATEADD('Table'[Date],-1,YEAR))
Prior Period Sales 2 = CALCULATE(SUM(MyTable[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))

    Note:

    • The date in tables is from calendar table.
    • Due to we calculated the sales of previous year, it only can be shown as year and entire date, like the pic above. We can't drill down to quarter , month and day.

    I attached my sample that you can reference.

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi GMS0101 ,

    You need to create a new calendar table and create a relationship between the two tables.

    Table = CALENDARAUTO()

    Prior Period Sales   = CALCULATE(sum(MyTable[Sales]),DATEADD('Table'[Date],-1,YEAR))
Prior Period Sales 2 = CALCULATE(SUM(MyTable[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))

    Note:

    • The date in tables is from calendar table.
    • Due to we calculated the sales of previous year, it only can be shown as year and entire date, like the pic above. We can't drill down to quarter , month and day.

    I attached my sample that you can reference.

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • GMS0101's avatar
      GMS0101
      Frequent Visitor
      7 years ago

      Thank you for responding Xue! This makes sense.

       

      One last question:

       

      Let's say I wanted to put the same measure for total sales in prior year into a card visual and let's use this measure:

      Prior Period Sales 2 = CALCULATE(SUM(MyTable[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))

      How would I get the card visual to show PY revenue instead of cumulative? Or would I use a different measure to do this? As far as using the card, I can accomplish this using TOTALYTD but not sure if that was the intended way of doing this. 

       

      Thank you soooooooooo much!

      -Garrett 

       

      • v-xuding-msft's avatar
        v-xuding-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi GMS0101 ，

        How do your measure of Total Sales like?  Can you share it?

         

        I created a new formula. 

         

        Calculate = CALCULATE(SUM(MyTable[Sales]),FILTER(ALL(MyTable[Date]),MyTable[Date] <=MAX(MyTable[Date])&&YEAR(MyTable[Date])=YEAR(MAX(MyTable[Date]))))

        Is this what you want? If not, please share your expected output.

         

        Best Regards,

        Xue Ding

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.