Forum Discussion
Last year calculation does not work properly
Hello,
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?
Thanks.
2 Replies
Hello,
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.
