Forum Discussion
Filtering on multiple dates using a single date slicer
Hello v-frfei-msft
Sorry for the late response. I could meet the above requirement by using "Pivoting". I transformed all my date columns into row clubbed them under "Activity Date" and used this Activity date in my date slicer.
Hi Anonymous,
I made one sample for your reference.
1. Enter the data and create a dimtime table using the formula. And new a calculated column in the dimtime table.
Dimtime = CALENDARAUTO()
received date = 'Dimtime'[Date]-60
2. Create the reslationship between the two tables like this.
3. Create a measure and create the visuals like this. Then we can get the result as we excepted.
Measure = CALCULATE(SUM('fact'[amount]),USERELATIONSHIP('fact'[shipped data],'Dimtime'[Date]))
For more details, please check the pbix as attached.
https://www.dropbox.com/s/dt8hg1bahrt2afr/filter.pbix?dl=0
Regards,
Frank
Hi Frank,
Thank you for the detailed response. I do not want an additional recieved column which has an offset of 60 days. I basically want one date slicer which should filter Ordered Date, Recieved date and Shipped date. For example, If I select a date range of Feb 1st to Feb 8th, I want it to show me details of my orders with ordered date and shipped date between the range selected in slicer(i.e. Feb 1st to Feb 8th) and Recieved date 60 days before Feb 1st till Feb 8th.
Hi Anonymous,
I made an update based on the previous pbix.
Firstly, create the relstionship between tables like this.
Then create the measures as below.
ship amount = CALCULATE(SUM('fact'[amount]),USERELATIONSHIP('fact'[shipped data],'Dimtime'[Date]))rece amount = CALCULATE(SUM('fact'[amount]),USERELATIONSHIP(Dimtime[received date],'fact'[received date]))Here is the result for your reference.
For more details, please chekc the pbix as attached. If it doesn't meet your requirement, kindly share your sample data and excepted result to me.
https://www.dropbox.com/s/dt8hg1bahrt2afr/filter.pbix?dl=0
Regards,
Frank
Hi Anonymous,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
Hello v-frfei-msft
Sorry for the late response. I could meet the above requirement by using "Pivoting". I transformed all my date columns into row clubbed them under "Activity Date" and used this Activity date in my date slicer.