Daily Data Vs. Benchmark From Two Querries

[SolarGuru]

Hi,

I'm still relatively new with PowerBI and I'm hoping I can get some help with creating a specific measure and graph that haven't been able to crack so far.

I have two queries.  One pulls a sharepoint data populated with daily kWh generated by a Solar PV system.  The table includes 1 entry per day from many different PV systems. This part seems straight forward. 

The second query pulls data from another Sharepoint table.  This table pulls a benchmark for how much generation a system is expected to generation in a given month.  The table contains multiple projects and 12 rows (1 per month) for each project.  Each project's 12 rows is dated 1/1/2018-12/1/2018 if the entries in this table were created in 2018.  It will have 1/1/2019 - 12/1/2019 if the entries were created in 2019.  It will never have entries in both 2018 and 2019.  From this data, I can estimate how much data is expected in a given month by dividing monthly amounts by the number of days in the month.

I would like to create an bar graph that allows the user to show the last 90 days of daily production from one speciic project (Query 1) compared to the relevant benchmark for that day.  At risk of stating the same thing over, I would have 90 data points for actual production and 3x30(ish) bars for each month's benchmark.  

I have so far created one table by appending Query 1 to Query 2 and merging the data so that I have a column with date, production, project name, and whether that row is a benchmark amount or an actual amount.

When I try to plot these items, no surprise, the daily benchmark always shows up on the first of the month.  If I were to limit my data set to 30 days and plot the actual daily data against the trendline/average of the single benchmark dataset, then I'm getting close to seeing the visual I want.  But, this quick fix will only work on exactly 30 days since it is the average of only one benchmark data point.  As I cross months, the trendline will become the average of mulitiple benchmarks whereas I always want to compare each daily production to that month's daily benchmark.

Any pointers on how I can create a measure for the appropriate month's daily benchmark that when plotted on a daily chart will give me a bar on each day as opposed to just the first of the month?

Thanks in advance!

[v-xuding-msft]

Hi @SolarGuru ,

Can you please post a dummy file or post some sample data and the expected result? Then we can help you as soon as possible.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Xue

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SolarGuru]

---

@v-xuding-msft wrote:

Hi @SolarGuru ,

Can you please post a dummy file or post some sample data and the expected result? Then we can help you as soon as possible.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Xue

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

---

Thank you for the quick reply and the guidance.  

Since I can't post the actual data from sharepoint, here is a link to a mockup in excel: 

https://drive.google.com/file/d/1xcEFfZRz4yj_DrQbFM1VBjomFqQG8yTr/view?usp=sharing

First tab is the first query for the benchmarks.  Second tab shows sample results of the query for the actual data.  The third tab shows the results I'm trying to achieve as numbers and a graph.  I've also color highlighted the rows in each of the queries that I'm drawing on to make the result.  

Many thanks!