Forum Discussion

Soccermet3's avatar
Soccermet3
Frequent Visitor
3 years ago
Solved

Line Graph Aggregating Data when it should be blank

Hi All, I am using a date hierarchy on the X-axis but have removed everything except for Month name as that is the only thing that I would like to be displayed. The Y-axis is displaying energy usag...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    Ah I see the issue. If you want to report on things that are not there you need to use disconnected tables and/or crossjoins.

     

    You need a disconnected table with the months. Then you can show the measure for the current range, the range of 12 months ago, and the range of 24 months ago.

     

    Dates = ADDCOLUMNS(CALENDAR(EDATE(TODAY(),-11),TODAY()),"Month",FORMAT([Date],"mmmm"),"YearMonth",FORMAT([Date],"yyyymm"))
    Prior year = 
var p = edate(min('Dates'[Date]),-12)
return CALCULATE(sum(data[Value]),ALL(data),data[Date]=p)
    Prior prior year = 
var p = edate(min('Dates'[Date]),-24)
return CALCULATE(sum(data[Value]),ALL(data),data[Date]=p)

    see attached

     

Soccermet3's avatar
Soccermet3
Frequent Visitor
3 years ago

Great catch on having redundantcy in my measure, however, removing the SUMX (because it was already pulling in a measure that summed multiple columns) did not change the result. See below:


R12 Total Energy Use (MWh) = CALCULATE('All Data'[Total Energy Use (MWh)], DATESINPERIOD('All Data'[Date], LASTDATE('All Data'[Date]),-1,YEAR))
 

 

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Can't help further without sample data in usable form.

    • Soccermet3's avatar
      Soccermet3
      Frequent Visitor
      3 years ago

      Ibendlin,
      here is a snippet of what the data looks like.

      FacilityDateElectricity (MWh)Fuel Oil Usage (MWh)Solar Generation (MWh)Natural Gas (MWh)
      Facility16/1/2022461.99499990143.84396.3719031
      Facility26/1/20221261.12090.731354.016735
      Facility15/1/2022397.20299990131.1614.79655611
      Facility25/1/20221067.627084.781524.25038
      Facility14/1/2022418.78599990100.31608.6512081
      Facility24/1/20221156.177082.242036.738624
      Facility13/1/2022439.11699990125.55797.959226
      Facility23/1/20221055.4183.51127266662.222094.577043
      Facility33/1/2022162.2025.170709748 112.6523347

       

      The data dates back to 2017 as a whole. The file can be accessed here: Example Data 

       

      Thank you!

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        Thank you for providing the sample data. That helps a lot with proposing a potential solution.

        Here is the approach for "Rolling 12 months" .  Please explain what you mean by "Previous Rolling 12 Months" 

         

        see attached