Forum Discussion
Line Graph Aggregating Data when it should be blank
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
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().
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!