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