Monthly As-On report status using multiple history tables

[aj1107]

Hi,

I'm trying to create a report with underlying data source (In screeshot Table A & B). Table A & B contains history of latest ID records for each month. I've to create a report (sample screenshot) by joining the two tables and create as on status by showing Month-Year in X-axis (last 6 month status). Filtering can be applied on either ways (table A/B) in some of the reports.

Below chart shows - open issues as on each month and corresponding action status in column series and values. Ive created a model in data source by joining two tables (similar to monthly snapshot) to get the as-on status of month in X-axis.

But the dowside with this model is, i've to replicate the status to each month to fill the gap and join the two history atables. I'm afraid of query performance and storage, since it resembles the monthly snapshot view.

Is there a better way tomodel the source table A & B with out monthly snapshot approach and get the chart as shown in screenshot.   Most of the report requirements are similar to below chart (with As-On month in x-axis) and filter by multiple history tables.

Thank you.

[v-ljerr-msft]

Hi @aj1107,

---

I'm trying to create a report with underlying data source (In screeshot Table A & B). Table A & B contains history of latest ID records for each month. I've to create a report (sample screenshot) by joining the two tables and create as on status by showing Month-Year in X-axis (last 6 month status). Filtering can be applied on either ways (table A/B) in some of the reports.

After a few try, I still haven't found any better solution in this scenario. It seems to be that your solution may be the best one here. 

Regards

[aj1107]

Hi @v-ljerr-msft,

Thanks for sharing your views. Small change in table A. Row 2 - I1, 31-mar.

I tried to import the table A & B in power BI as it is( to avoid mothly snapshot as shown in existing model) and then use DAX to get the desired chart. But it looks complex to me to join two history tables and get the as-on status on monthly axis. Hence chosen SQL to build the model at datasource level.

Thank you again. Keep me posted if you come across similar scenario with better solution