Filtering on multiple dates using a single date slicer

[Anonymous]

Hello,

I have 3 dates within my table ie. Ordered date, shipped data and recieved date. I need to filter all the 3 dates using one date slicer. Data in Power BI is being pulled from SSAS tabular model(direct mode in SSAS also). So if I select a date range from Feb 1st- Feb 8th in the date slicer, I need Power BI to show me data for ordered date and shipped date from Feb1st to Feb8th and Received date should also show data 60 days prior to Feb1st(i.e. from December1st- Feb 8th). How do I achieve this?

Thanks in advance

[v-frfei-msft]

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

[Anonymous]

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. 

[v-frfei-msft]

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

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,

Frank

[Anonymous · Accepted solution]

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.

[jthomson]

You'd make a date table as normal, and relate that to your ordered and shipped fields to it. Then, in your date table, make a custom column that reflects the 60 day offset, and relate that new column to your received date. Sticking the first column from your date table into your slicer should then do the trick

[Anonymous]

Hi,

I am not able to give relation twice to the same date.I am able to fetch minimum date value from the slicer range but not able to obtain the 60 days offset using this min date value.

Thanking in advance.