Filter a table based on date

[Anonymous]

Hello everyone!

In a table, I have a list over trailer ID numbers. Next column is "loaded date", showing when the trailers was used. But I want to filter the table so only the LATEST entry is show. As circled, for trailer DBN885 I only want to see the row with "05.01.2020" date. The two columns comes from two different (linked) tables, if that matters. Thanks in advance for any quick tips!

Regards,

Robert O

[MartynRamsden · Accepted solution]

Hi @Anonymous 

Click on your table visualization, then, in the Visualizations panel, click on your Date column and select 'Latest'.

That should summarize your table and only show the max loaded date for each trailer.

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[Anonymous]

Hi @MartynRamsden 

Thanks, it sort of did the trick. Initially it just picked up the latest date in my filter, but when adjusting the cross filter direction between the tables from "single" to "both" it worked.

[MartynRamsden]

Hi @Anonymous 

Be very cautious when enabling bi-directional relationships between tables in your data model as you can very quickly end up with incorrect results! You can read more about this here: www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/ 

As an alternative solution, you could create a measure instead, which enables the bi-directional relationship only for the life of the calculation.

Something like this:

Last Loaded Date = 
CALCULATE ( 
    MAX ('Calendar'[Date] ),
    CROSSFILTER ( 'Calendar'[Date], Trailer[Trailer Number], BOTH )
)

You'll obviosuly have to replace the column references in the CROSSFILTER function with the correct columns references from either side of the relationship in your model.

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[Anonymous]

Hi again. OK so I have progressed a bit, but might need to take a step back;

I have two tables, where this is the columns in question:

"fact_Orders"

Order ID | Loaded Date

"dim_Trailers"

Order ID | Trailer Number

The tables are linked on "Order ID", many-to-one single direction.

Each new order gets assigned a trailer number and loading date from the order system.

I need a visual (table) "Last Used" looking back on for example last 30 days (visual filter), listing each "Trailer Number" which have been used last 30 days, and the LATEST "Loaded Date" for that trailer. So each trailer should appear only once in my "Last Used" table. In addition, a calculation of how many days since "Last Loaded" (from todays date). So output:

Visual: Table, "Last Used"

Trailer ID | Last Loaded | Days Since Last Loaded

I have managed (with tips above) to filter out only the latest loading dates, but have not been able to calculate "days since last loaded". 

Any help out there? Thanks in advance!