cancel
Showing results for
Did you mean:
Highlighted
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

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

Hi,

Hope this helps.

5 REPLIES 5
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.

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

Hi,

Hope this helps.

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.

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

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.