Forum Discussion
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 usage in a Rolling 12 month, Previous rolling 12 month, and a specific fiscal year for comparison sake.
The table of data is shown on the left of the image below. The table shows that there are BLANK() values for CURRENT YEAR after March. The graph shows up to June. I was anticipating that April/May/June for the dark blue line would not exist. Instead it appears Power BI is agregating previous years data for those months. The other months are showing up with their correct totals.
The measure for the line is as follows:
Thank you!
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
12 Replies
CALCULATE(SUMX(That is likely the culprit. Did you mean to say SUM instead of SUMX ?
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216Great catch on having redundantcy in my measure, however, removing the SUMX (because it was already pulling in a measure that summed multiple columns) did not change the result. See below:
R12 Total Energy Use (MWh) = CALCULATE('All Data'[Total Energy Use (MWh)], DATESINPERIOD('All Data'[Date], LASTDATE('All Data'[Date]),-1,YEAR))Can't help further without sample data in usable form.
Ibendlin,
here is a snippet of what the data looks like.Facility Date Electricity (MWh) Fuel Oil Usage (MWh) Solar Generation (MWh) Natural Gas (MWh) Facility1 6/1/2022 461.9949999 0 143.84 396.3719031 Facility2 6/1/2022 1261.12 0 90.73 1354.016735 Facility1 5/1/2022 397.2029999 0 131.16 14.79655611 Facility2 5/1/2022 1067.627 0 84.78 1524.25038 Facility1 4/1/2022 418.7859999 0 100.31 608.6512081 Facility2 4/1/2022 1156.177 0 82.24 2036.738624 Facility1 3/1/2022 439.1169999 0 125.55 797.959226 Facility2 3/1/2022 1055.418 3.511272666 62.22 2094.577043 Facility3 3/1/2022 162.202 5.170709748 112.6523347 The data dates back to 2017 as a whole. The file can be accessed here: Example Data
Thank you!
You may have noticed that I calculate "Rolling 12 months" slightly differently. I don't assume that I have data for all 12 month, rather I do an average over the available data. (Could have used AVERAGEX, I guess). Anyway, all you need to do is adjust the EDATE parameter:
Previous Rolling 12 Months = var md = max(data[Date]) var a = CALCULATETABLE(SUMMARIZE(data,data[Date],"sm",sum(data[Value])),data[Date]<=EDATE(md,-12),data[Date]>EDATE(md,-24)) return divide(sumx(a,[sm]),COUNTROWS(a),0)see attached
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!
I think we have a terminology disconnect. Did you mean to compute averaged year over year numbers?
