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

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

Hi GMS0101 ,

Oh, I understand clearly this time. After testing, your formula will work when I use the date from calendar table. Do you try this? Did it work in your sample?

YTD SALES = TOTALYTD([Total Sales],'Table'[Date])
Measure = CALCULATE([YTD SALES], SAMEPERIODLASTYEAR(MyTable[Date]))

I attached my sample that you can download.

 

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

Those formulas work for me.

 

I just wanted to see if there was a way  to use the original combination of SUM() and SAMEPERIODLASTYEAR as I saw others saying it was possible so I just wanted to verify if that was the case or not.

 

Thanks for all your help Xue! Seriously appreciate all the help!