Forum Discussion

Matjo's avatar
Matjo
Icon for Advocate I rankAdvocate I
8 years ago
Solved

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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    You may download my PBI file from here.

     

    Hope this helps.

     

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
8 years ago

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's avatar
Matjo
Icon for Advocate I rankAdvocate I
8 years ago

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

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