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

     

lbendlin's avatar
lbendlin
Super User
3 years ago

I think we have a terminology disconnect. Did you mean to compute averaged year over year numbers?

Soccermet3's avatar
Soccermet3
Frequent Visitor
3 years ago

its not an average but a SUM of the last 12 months of energy usage. However, I can calculate the correct values without any issue. The problem is on a line graph they do not display correctly as if a facility is missing data from 2022 then it uses the data from the previous year to display, instead of displaying BLANK(). 

  • lbendlin's avatar
    lbendlin
    Super User
    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

      Awesome thank you! This got me to the result I needed!