How to calculated "consecutive" years of revenue reporting

[Chuky01]

Hi all, I have a table (call it "Table") with a "Year" column (2016 - 2019), "ID" column (for each location) and other columns like "Sales", "Revenue" etc. How do calculate a measure to count the number of consecutive years where a location had Revenue  (some location IDs have blank Revenue for some years). Essentially, I'm trying to find - 

• how many location IDs have 4 years of consecutive Revenue 
• how many location IDs have 3 consecutive years of Revenue (2019, 2018, 2017)
• how many location IDs have 2 consecutive years of Revenue (2019, 2018,)
• how many location IDs are reporting for the first time in 2019. 

[Jihwan_Kim · Accepted solution]

Hi, @Chuky01 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

I tried to follow your idea and applied it to the sample pbix file.

All measures are in the sample pbix file, and you can follow the steps.

https://www.dropbox.com/s/hxsrkib5hmgrchm/chuky01.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/