Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
powerbiss
Helper I
Helper I

Previous Year Sales

Hello,

I am trying to calculate previous year's sales with the below sample data. I used the two formulas and didnt get the right answers. I also have a date table from 2018 to 2022 with date and year column.


With the below formula, I am getting blank. 

Sales Last Year = CALCULATE(SUM(sales[Sales]), PREVIOUSYEAR(sales[Date]))
 
With the below formula, I am getting sales from 2018 to 2021
New_Last_Year = CALCULATE(SUM(sales[Sales]), DATEADD('Date'[Date], - 1, YEAR))
 
previous year sales.png
 
Thanks for all your help
 
 
1 ACCEPTED SOLUTION

Hi, @powerbiss 

Try measuse as below:

Last Year = 
CALCULATE (
    SUM ( sales[Sales] ),
    FILTER ( 'sales', YEAR ( sales[Date] ) = YEAR ( TODAY () ) - 1 )
)

veasonfmsft_0-1653387765456.png

 

Best Regards,
Community Support Team _ Eason

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Did you link your date table to the sales table ?

Hi James,

Yes created a relationship between Date table and Sales table. Did you get 1600

 

Thanks 

Hi, @powerbiss 

Try measuse as below:

Last Year = 
CALCULATE (
    SUM ( sales[Sales] ),
    FILTER ( 'sales', YEAR ( sales[Date] ) = YEAR ( TODAY () ) - 1 )
)

veasonfmsft_0-1653387765456.png

 

Best Regards,
Community Support Team _ Eason

Thank you v-easonf. Worked great. Really appreciate your help

Anonymous
Not applicable

the table Feuil5

JamesFr06_0-1653236338943.png

Measure#1 ==> 

Sales Y = calculate(sum(Feuil5[Sales]))
Measure#2 ==> 
Sales PY = calculate([Sales Y],SAMEPERIODLASTYEAR('Date'[Date]))
 
and the final result
JamesFr06_1-1653236421862.png

 

Hi James,

That worked for me. Thanks for your help

Anonymous
Not applicable

 

 

First measure 

Sales=CALCULATE(SUM(sales[Sales])

second measure ==> 

Sales PY=Calculate([Sales],SAMEPERIODELASTYEAR(Date[date])

Hi James,

I am still get 6600 instead of 1600

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.