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

The measure you created does return the exact same values as what my measures did. However, the measure still returns data beyond the date range that we are filtering for in this case. Because of this Power BI still aggregates prior years data in the linegraph as what the original problem shows.
See screenshot below:

The measure in purple is a "copy" of what you presented in this thread.

 

Thank you!

 

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!