# Filter out years with incomplete data?

• ### Question

• 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

Thursday, November 14, 2013 10:18 AM

• I think you can use DISTINCTCOUNT function for this.  Something like

```=CALCULATE(DISTINCTCOUNT(EnergyUsage[Date]),FILTER(EnergyUsage,EARLIER(EnergyUsage[Year]) = EnergyUsage[Year]))
```

Then use it in total sum calculation

`TotalSum:=CALCULATE(SUM(EnergyUsage[UsageVal]),FILTER(EnergyUsage,EnergyUsage[CalculatedColumn]=365))`

• Marked as answer by Thursday, November 14, 2013 9:39 PM
Thursday, November 14, 2013 12:06 PM

### All replies

• I think you can use DISTINCTCOUNT function for this.  Something like

```=CALCULATE(DISTINCTCOUNT(EnergyUsage[Date]),FILTER(EnergyUsage,EARLIER(EnergyUsage[Year]) = EnergyUsage[Year]))
```

Then use it in total sum calculation

`TotalSum:=CALCULATE(SUM(EnergyUsage[UsageVal]),FILTER(EnergyUsage,EnergyUsage[CalculatedColumn]=365))`

• Marked as answer by Thursday, November 14, 2013 9:39 PM
Thursday, November 14, 2013 12:06 PM
• Many thanks for your solution which works perfectly.
Thursday, November 14, 2013 9:42 PM