Forum Discussion
Snapshot data in Power BI
Hi Anonymous,
Could you please share sample data of your scenario and post the expected result? Also we need to know that what visualizations you are using.
Thanks,
Lydia Zhang
Hi Both,
Thanks for your replies. Sure, here's some simple data replicating my scenario. The eventual model would have multiple tables but the two most relevant tables are:
DIM_DATE and
FACT_PLANS
DIM_DATE is a date dimension, like:
| Date | Year | Month | Day |
| 01/03/2016 | 2016 | 3 | 1 |
| 02/03/2016 | 2016 | 3 | 2 |
| 03/03/2016 | 2016 | 3 | 3 |
| 04/03/2016 | 2016 | 3 | 4 |
| 05/03/2016 | 2016 | 3 | 5 |
| 06/03/2016 | 2016 | 3 | 6 |
| 07/03/2016 | 2016 | 3 | 7 |
| 08/03/2016 | 2016 | 3 | 8 |
| 09/03/2016 | 2016 | 3 | 9 |
| 10/03/2016 | 2016 | 3 | 10 |
| 11/03/2016 | 2016 | 3 | 11 |
| 12/03/2016 | 2016 | 3 | 12 |
| 13/03/2016 | 2016 | 3 | 13 |
| 14/03/2016 | 2016 | 3 | 14 |
and FACT_PLANS is a table like the below (with 1 million rows)
| Client ID | Plan Type | Plan Start Date | Plan End Date |
| 1 | A | 01/01/2014 | 31/03/2016 |
| 2 | B | 01/03/2016 | 05/03/2016 |
| 3 | C | 02/03/2016 | 12/03/2016 |
| 4 | D | 01/04/2014 | 05/01/2015 |
| 5 | B | 31/05/2010 | 08/03/2016 |
| 6 | D | 01/08/2011 | 01/11/2015 |
| 7 | E | 01/05/2015 | 09/05/2016 |
| 8 | A | 14/07/2015 | 11/03/2016 |
| 9 | A | 14/05/2015 | 31/03/2016 |
| 10 | C | 15/05/2013 | 14/04/2016 |
What I would like to do is work out the number of plans open on any given day, for an expected result like:
| Date | Number of plans open |
| 01/03/2016 | 7 |
| 02/03/2016 | 8 |
| 03/03/2016 | 8 |
| 04/03/2016 | 8 |
| 05/03/2016 | 8 |
| 06/03/2016 | 7 |
| 07/03/2016 | 7 |
| 08/03/2016 | 7 |
| 09/03/2016 | 6 |
| 10/03/2016 | 6 |
| 11/03/2016 | 6 |
| 12/03/2016 | 5 |
| 13/03/2016 | 4 |
| 14/03/2016 | 4 |
What is the most performant way to dynamically claculate this using Power BI desktop, for any given date? From a visualisation perspective I'm thinking a plain bar or line chart.
Thanks!
Pbix