Forum Discussion
Snapshot data in Power BI
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
Hi Anonymous,
You can create a measure named plan number in your FACT_PLANS table using the following formula. I test it in my environment which is shown in the screenshot below .
plan number = CALCULATE(COUNTROWS(FACT_PLANS),
FILTER(FACT_PLANS, (FACT_PLANS [Plan Start Date] <= LASTDATE(DIM_DATE[Date])
&& FACT_PLANS [Plan End Date]>= FIRSTDATE(DIM_DATE[Date]))))
Then create line chart by using the Date field and plan number measure as follows.
Thanks,
Lydia Zhang
Hi Anonymous,
Thanks for the reply - really helpful. However, I'm having trouble replictating it in my environmnent - for me the measure returns a count of the total number of rows in the table in every row (though your example clearly works)!
What relationship are you using between FACT_PLANS and DIM_DATE?
Thanks alot,
Pbix
Hi Anonymous,
Sorry, I was being an idiot - I was creating a calculated column, not measure! Oops...
When you have a moment, I'm still interested though - what relationship(s) did you define between DIM_DATE and FACT_PLANS? I ask because I current use DIM_DATE(date) and FACT_PLANS(Plan Start Date) and it doesn't quite return the correct values.
Thanks again :)
Pbix
Hi Anonymous,
I just copy your sample data and enter it to Power BI Desktop, no relationship is created between the tables. What is the relationship between your tables? And what does your measure look like?
Thanks,
Lydia Zhang