filter a dashboard using different Date fields for different visuals.

[johnpw]

full disclousre, I have been using Tableau with some demo licesnes and just found out my company has a licesne for Power BI.

So this afternoon I buly a pretty cool (for me) dashboard with 4 visualizations

1) shipping   which has a Actual ship date one ach record

2) backlog    which has a Schdeuld ship date on each record

3) forecast which has an opportunity close date on each record

4) transcation which has a transaction date on each record.

this seems to be working pretty cool but I'd like to be able to filter it on just this quarter, or last quarter etc where the user might select "This quarter" and the visual for shipping would use that filter for teh Actual Ship date and the back log would use this quarter for Schedule ship date etc.

I've seen some things about creating a calendar table but that seems to be when you have 1 record with 2 different dates where I'm pulling from different objects.

nots ure if my terminology is going to cause confusion

thanks

john

[johnpw]

thans I watched some youtube videos on date tables and created one and then created a link to my Opportunity table, my Transaction table, my Shipment table but my Order product table where my schedule ship date is gets an error 

[danextian · Accepted solution]

There are multiple potential ways to traverse relationships between tables in your data model, leading to confusion or incorrect results. 

• Order Product Shipment > Order Product > Calendar Dates
• Order Product Shipment > Calendar Dates

Normally, you would want it to be

• Calendar > Order Product Shipment
• Calendar > Order Product
• Order ID > Order Product Shipment 
• Order ID > Order Product > Order Details

In here I am assuming Order Product is when the orders were made and Order Product Shipment is another table for shipment.

But then these are just assumptions. You did not provide  a sample data. https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/144... 

[danextian]

Hi @johnpw 

You indeed need to use a separate dates table so you can filter different dates using the same  date column. There can only be one active relationship betweeen two tables so the extra ones will be inactive can be invoke by using USERELATIONSHIP in a measure.  Active relaitonships automatically filter the visible rows without needing to specify the relationship in a measure.

As to being able to select this quarter only, you can use the relative date feature of the date slicer. There is no option for quarter so the user must select x months depending on the current date. Calendar always ends at the current month's ending date. The other months option ends at today's date goes back to 3 months prior + one day.  If you dont have data in the last x period from the reference date, of course, no value will be returned.

If you want to have a slicer that switches between this quarter and not, you can include a calculated column in your dates/calendar table.

IsCurrentQuarter =
IF (
    QUARTER ( [Date] ) = QUARTER ( TODAY () )
        && YEAR ( [Date] ) = YEAR ( TODAY () ),
    TRUE (),
    FALSE ()
)

Please see attached sample pbix.

By the way, going back to the license, a pro license is required to either be a viewer or an author of a shared report.