none
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

Answers

  • 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 Russ888 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 Russ888 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