Snapshot data in Power BI

[pbix]

Hi All,

I have a question about modelling data using Power BI.

In my industry we frequently report on the number of clients open to a staff member, team or service on any given day - e.g. there were 500 clients open to the team on 31st May 2016.

What is the best approach for managing these types of data (which typically can't be aggregated as it just represents a snapshot position on any given day) in a cube? I've found that Power BI tries to incorrectly aggregate these data in visualisations (e.g. aggregate 12 month end snapshots into a summed single year figure, which isn't correct).

Thanks

Pbix

[v-yuezhe-msft]

Hi @pbix,

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

[pbix]

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

[v-yuezhe-msft]

Hi @pbix,

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

[pbix]

Hi @v-yuezhe-msft,

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

[pbix]

Hi @v-yuezhe-msft,

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

[v-yuezhe-msft]

Hi @pbix,

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

[ankitpatira]

@pbix I would say you can use table or matrix visual to represent such data and make sure you set fields as Do Not Summarize. This will give you raw data without aggregation. You can also use other visuals which support Do Not Summarize.