Filter current year but not prior year


I want to create a table of contract values - current active contract vs prior year contract where the contract has been renewed.

I think I’ve calculated the PY and variance correctly however when I filter the table for renewals only it also removes the contracts that weren’t renewals that make up the PY value

ie Contract A 2019 - 2020 Value £1000 is a new contract - it renews 2020-2021 £1200 and is marked as a renewal contract
Contract B is a new contract 2020-21

I want to calculate a table of renewals only ie
Contract Current £ PY £ Variance
Contract A 1200 1000 200

At the moment becuase I’m filtering on Renewal True/False it excludes the original contract A value

How can I fix this?

C Anal Test.pbix (177.1 KB)

Thanks for posting your question @AliB. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Hi @AliB, we’ve noticed that no response has been received from you since the 22nd of October. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Hi @AliB, due to inactivity, we have tagged this post as SOLVED. If you feel the need to activate the post, we suggest creating a new topic to get a better response from the community. Thanks!

I believe that I have found the issue - you needed to be able to ignore the Renewal filter in the prior year measure:

Revised Contract PY =
CALCULATE( [Contract £ PY], ALL( testdata[Renewal] ))

updated copy of your PBIX is attached (see the page 2 tab for resolution)C Annual Test (updated).pbix (167.6 KB)

1 Like

Brilliant! - thank you Heather

always happy to help :slight_smile:
Sorry I didn’t see your issue sooner, I’ve been involved in some major non-PowerBi projects at work lately