Forum Discussion
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...
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
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
Awesome thank you! This got me to the result I needed!