Thanks.  

It appears There’s just one more little quirk

for contracts that expire in the future I need to also check their status as being active. How can I do this ?

With another && criteria.

Will it have to be another IF scenario for expired and Unexpired contracts that duplicates what we have already but with the additional && criteria for unexpired contracts

i only need to check future contracts for the active status not all contracts as the previous years(ie expired) will all be set to closed

Probably better to ask Matt Alington this question: https://powerpivotforum.com.au/

Just a thought...I presume you've tried adding the status field to the PivotTable and filtering on 'Active' contracts only?

All seems to be working  - thank you.

However, now that we have created a line for each year of a contract eg a 5 year contract now has 5 lines instead of 1 my measure for Active contracts now reports contracts that expired back in 2017 for eg as still being active in the [System Status]

What would be the most efficient way to re-calculate this measure:

Create a new query referencing Table1 filtered for [System Status]="Active" and delete duplicate rows, or

Create a new measure

Active:=CALCULATE([Total Revenue],FILTER('Table1',[System Status]="Active"&&[Revenue Year]>=TODAY()))

or

Something else?

Using the data in the file you sent.   As per the first post in this trail I'm using POWER BI

I've recreated all the queries within Power BI - but

If I use a matrix filtered for 2016, 2017 and 2018 and put the Inc Customer value, Lost Customer Value, New Customers Value and Reduced Customer Value fo the values the Total line does not add up to the total of the 3 years.

How can I recreate the waterfall chart in Power BI