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