Forum Discussion
Advocate ISlicing 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
5 Replies
v-frfei-msftCommunity Support
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
Matjov-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
Super User
You are welcome. In the Query Editor, you should be able to filter out the NULL values.
- Ashish_Mathur
- Matjo
Ashish_Mathur very easy to understand solution it helped me understand more about power query. Thanks.