Two data granularities overlayed

Hi all,

I'm trying to make a stock analysis tool and therefore I would like to be able to see daily data such as stock price but at the same time also Net Income or Operational Cashflow on Quarterly level. It should look similar to the below where I'm overlaying two charts with no background so you can see through them but cannot click or hoover on the chart in the background.

Does anybody have an idea on how to do this?

I have three tables at the moment:
- Pricing table with closing prices on date level

- Income statement table with quarterly data

- Date table so I can create relations with the above tables.

I linked to Princing Table to the Date Table by the dates. I linked the income statement table to the date table by the quarters.

I don't believe hierarchies can help here because I need to be able to see two levels hierarchies at the same time. The stock prices will take the avg (or max/min) over a quarter when you drill up to quarterly level while the Operational Cashflow will become empty (or flat, I can't recall very well) when drilling down to date level, since there's only one date data point per quarter.

I have an ugly solutions but I'm not happy with it. I extrapolate the Operational Cashlfow to all dates of the quarter DAX (except for the first and last dates of the quarter). However, it can lead to vertical stripes based on zooming AND I cannot add other , measures such as Free Cash Flow or NOPAT next to it, as these other bars will pop up in between the bars of the Operational Cashlfow.

Does anybody have an idea on how to create the overview as described in the beginning?

Examples can be found on finchat.io as well.

Thanks in advance!

Need Help

Tips and Tricks

Anonymous
1 year ago
Hi JasperVanGils ,
This doesn't make sense, in the Line and clustered column chart, the number of turning points and bars of the folded line are displayed based on the granularity on the x-axis, so their granularity needs to be consistent. You could set the quarter as a line, which would produce a baseline to measure each month against the average of the quarter, which would be statistically significant. Bars, on the other hand, show values by date or totaled by quarter, so that you can differentiate how much granularity there is at different times.

Best regards,
Albert He

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

5 Replies

Anonymous
Not applicable
1 year ago
Hi JasperVanGils ,
According to your description, regarding visualization, you can use Line and clustered column chart. since it needs to be yes they have the same date granularity, you can create a corresponding quarterly profit column on calendar table. Here are the detailed steps
Sample data
Pricing table

Date Closing Prices

1/2/2024 521

2/4/2024 456

3/5/2024 123

4/6/2024 478

5/7/2024 598

6/7/2024 632

7/8/2024 458

8/9/2024 124

9/10/2024 523

10/11/2024 458

Income statement table

Quarter Date Average Price

3/1/2024 500

6/1/2024 400

9/1/2024 450

10/1/2024 600

Relationship

Create columns

Average_Price_Quarter = VAR _Quarter = QUARTER('Calendar Table'[Date]) RETURN CALCULATE( MAX('Income statement table'[Average Price]), FILTER( 'Income statement table', QUARTER('Income statement table'[Quarter Date]) = _Quarter ) )
Qtr = 'Calendar Table'[Date].[QuarterNo]

Final output

Best regards,
Albert He

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

Two data granularities overlayed.pbix57 KB
JasperVanGils
New Member
1 year ago
Hi Albert, thanks for looking into this with me. You're coming close but landing at the same issue I'm still having. If you're on Quarterly View, you still don't see the daily stock prices: each quarter only has an average stock price while I would like to see all data points plotted...

If I drill down to daily data, the quarters are plotted on each day. You cannot plot another measure in bars in that case as every day would then have two bars instead of each quarter having two bars.

Does this make sense?

You can also have a look here: https://finchat.io/company/NYSE-V/financials/cash-flow-statement/
Example screenshot:
- JasperVanGils
  New Member
  1 year ago
  Actually, I should get the below but the quarterly data plotted as bars and the bars aggregated over the full quarters iso bars on the quarter end dates:
- Anonymous
  Not applicable
  1 year ago
  Hi JasperVanGils ,
  This doesn't make sense, in the Line and clustered column chart, the number of turning points and bars of the folded line are displayed based on the granularity on the x-axis, so their granularity needs to be consistent. You could set the quarter as a line, which would produce a baseline to measure each month against the average of the quarter, which would be statistically significant. Bars, on the other hand, show values by date or totaled by quarter, so that you can differentiate how much granularity there is at different times.
  
  Best regards,
  Albert He
  
  If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
JasperVanGils
New Member
1 year ago
Hi Albert, thanks!

This helps in the sense that you confirm that plotting two data granularities in parallel is not possible with standard Power BI visuals. My 'issue' is not solved yet but I'll put this one as accepted.

Your help was very much appreciated!

Kind regards,
Jasper