Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

undefined

I am calcluating the running average of my data for over three years during the winter months.   

Rolling Average Winter = AVERAGEX(FILTER(ALLSELECTED('CALENDAR'[Date].[Date]),'CALENDAR'[Date].[Date]<=MAX('CALENDAR'[Date].[Date])-1095),[Winter_Demand])
 
[Winter Demand] is the sum of the usage from between November to March.
Winter_Demand = CALCULATE(SUM(METER_READS_W[USAGE]),FILTER('ADY by Year','ADY by Year'[PremID]),FILTER(METER_READS_W,METER_READS_W[SHORTREADDATE]),OR('CALENDAR'[Month]>10,'CALENDAR'[Month]<3))
 
I need to subtract the daily usage for the summer months (by day) from the Rolling Average Winter to get the amount used in the summer which exceeds the winter usage. Any help would be appreciated.
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    Based on your description, What are the months of summer that you would define, and it would be helpful to provide some sample data and expected results, I am a little confused that how do you want to calculate the  summer months, did it based on the measure[Rolling Average Winter ]?

     

    Best Regards!

    Yolo Zhu

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      THe months of March through October are a daily reading (1X day) 

      THe winter months Nov-Feb are a rolling average over three years soIf I pulled July 1 2023 the rolling average would be average all data from:

      2023 Jan & Feb, 2022 Nov, Dec, Jan, Feb, 2021 Nov, Dec, Jan, Feb

      When I run the script I have above it comes out correct but it reports it out as a daily average instead of the average for the year.

      The July 1 2023 data point is then subtracted by the Winter Running average:

      July 1, 2023 = 200 cf

      Winter Demand for the years in question = 100 cf

      Result should be 200-100 = 100 cf.

      Instead the winter demand only recognizes the winter values and will not report against the summer.

      So as a result the summer value July 1, 2023 has no subtraction.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        The graph above is how the winter demand is printing out and as you can see there is no values for the summer. I need the winter demand to reflecct the entire year value.