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

      • Soccermet3's avatar
        Soccermet3
        Frequent Visitor
        3 years ago

        - Rolling 12 months, as you know, is just the 12 months from the latest date in the dataset. Previous rolling 12 is the 12 months prior to the rolling 12. So this would display the last 2 years of rolling data in 2 chunks.

        - The X-axis in my case needs to display the month name ONLY for the graph. Using your date hierarchy if you only show month, does it show both rolling calculations correctly? Using the data I provided Facility 3 stops in March while other 2 facilities have data through June. This is where the issue is I think.