Line Graph Aggregating Data when it should be blank

[Soccermet3]

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:

R12 Total Energy Use (MWh) = CALCULATE(SUMX('All Data',[Total Energy Use (MWh)]), DATESINPERIOD('All Data'[Date], LASTDATE('All Data'[Date]),-1,YEAR))

Thank you!

[lbendlin]

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

[Soccermet3]

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]

I think we have a terminology disconnect. Did you mean to compute averaged year over year numbers?

[Soccermet3]

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 · Accepted solution]

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]

Awesome thank you! This got me to the result I needed!

[Soccermet3]

Great 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))

[lbendlin]

Can't help further without sample data in usable form.

[Soccermet3]

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!

[lbendlin]

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

Here is the approach for "Rolling 12 months" .  Please explain what you mean by "Previous Rolling 12 Months" 

see attached

[Soccermet3]

- Rolling 12 months, as you know, is just the 12 months from the latest date in the dataset. Previous rolling 12 is the 12 months prior to the rolling 12. So this would display the last 2 years of rolling data in 2 chunks.

- The X-axis in my case needs to display the month name ONLY for the graph. Using your date hierarchy if you only show month, does it show both rolling calculations correctly? Using the data I provided Facility 3 stops in March while other 2 facilities have data through June. This is where the issue is I think.

[lbendlin]

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-...