Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filtering on multiple dates using a single date slicer

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

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.

 

Capture.PNG

 

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]))

2.PNG

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/dt8hg1bahrt2afr/filter.pbix?dl=0

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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.

rela.PNG

 

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.

 

resul.PNG

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @Anonymous,

 

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

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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
Solution Sage
Solution Sage

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
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.