Slicing on a date when dates are represented by a range

[Matjo]

I have the following information about customers: 

Customer_id	Customer_type	Start_date	End_date
1	A	2017-01-01	2017-06-30
1	B	2017-07-01	2017-12-31
1	C	2018-01-01
2	A	2017-01-01	2017-09-30
2	B	2017-10-01

The dates define the period where the customer had the specified customer_type. I would like to create a report where I can select a date for example 2017-07-11 and see a list of how many customers that are A, B, C (in this case 1 A, 1 B)

Do I need to join and expand this table vs a calender or is there a better solution? 
I would also like to do a Sangkey diagram where I can define two dates and visualize how the customers stock has changed over that period and my guess is that I would need customer_type per day to do this? 

Best regards,
Mattias

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Matjo]

@Ashish_Mathur very easy to understand solution it helped me understand more about power query. Thanks. 

[v-frfei-msft]

Hi @Matjo,

We can take the folloing steps to meet your requirement.

1. Enter the data and create a dimtime table using the formula.

dimtime = CALENDAR(DATE(2017,01,01),DATE(2018,01,01))

2. Then create a measure and put it in the table visual and filter the table visual based on the measure.

Measure = IF(ISBLANK(MAX('fact table'[End_date])),BLANK(),IF(MAX('fact table'[Start_date])<=SELECTEDVALUE(dimtime[Date])&& MAX('fact table'[End_date])>=SELECTEDVALUE(dimtime[Date]),1,BLANK()))

3. Here is the result for your reference.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/qzmlds50j6y4vz0/slicing%20on%20a%20date.pbix?dl=0

Regards,

Frank

[Matjo]

@v-frfei-msft Thank you very much for your help! It works great except that it does not handle the null values in the column but that is fixed easy. 

[Ashish_Mathur]

You are welcome.  In the Query Editor, you should be able to filter out the NULL values.