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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Helper II
Helper II

Last year calculation does not work properly



I am trying to calculate last year revenue and orders but formula triggers different result than expected.


I also used sameperiodlastyear formula but it gives the same result. 
For total orders, i used distinct count of orders.

Could you please help? 


Super User
Super User


It looks like the issue with your formula might be that it's not filtering by the current year. When you use the TOTALYTD function, it returns a cumulative total of the measure for the specified year up to the specified date. However, in your formula, you're not specifying the current year, so it's returning the total for all years.

To fix this, you can add a filter to your formula to only consider the current year. Here's an example formula that you can use:

Last Year Revenue = CALCULATE( [Total Revenue], FILTER( ALL('Date'[Year]), 'Date'[Year] = YEAR(TODAY()) - 1 ) )

This formula uses the CALCULATE function to calculate the total revenue, but it also includes a FILTER function that filters the results to only include the previous year. You can use a similar formula for the total orders.

I hope this helps! Let me know if you have any further questions.

Hello, thanks for your reply. However, i've already tried to use this formula earlier and it returns value for last year which eventually affects all lines. The only problem is the last year value, the previous years are shown correct based on DATEADD or SAMEPERIODLASTYEAR. 



Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.