cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matjo Regular Visitor
Regular Visitor

Slicing on a date when dates are represented by a range

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Slicing on a date when dates are represented by a range

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

5 REPLIES 5
Community Support Team
Community Support Team

Re: Slicing on a date when dates are represented by a range

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.

 

Capture.PNG

For more details, please check the pbix as attached.

 

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

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Slicing on a date when dates are represented by a range

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

Matjo Regular Visitor
Regular Visitor

Re: Slicing on a date when dates are represented by a range

@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. 

Matjo Regular Visitor
Regular Visitor

Re: Slicing on a date when dates are represented by a range

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

Super User
Super User

Re: Slicing on a date when dates are represented by a range

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