Filter out years with incomplete data?

[Russ Skinner]

Trying to figure out a measure that will accomplish the following...

I have a single table in powerpivot that logs daily energy usage. Three columns: Date, Year , Energy usage. Several years worth of data, but some years do not have a full (365 days) worth of data. Current year a good example as we are only at November 13th.

I would like to be able to calculate the average annual (Jan 1st to Dec 31st) energy usage discarding data from incomplete years - need to filter them out. No data is simply recorded as blank cells in the table.

As a starting point I have thought about counting the non blank rows per year to identify those with less than 365, but how I then incorporate that into a measure to sum energy use and calculate the average consumption per year I have little idea?

Any ideas greatly appreciated.

Thanks,

Russ

 

[zestyphresh]

Assuming you won't be going back to add in historcal data regularly a calculate column may be the easiest way. Just add a column that counts rows per year (not sure how you handle leap years?) with a True/False condition for it being a full year. Then just use this as a page field/slicer in any reports.