I have the following information about customers:
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?
Solved! Go to Solution.
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.