Default filter Date Dimension

[admin_xlsior]

Hi,

When I have a Date Dimensions which currently up to year 2054, can I make it defaulted to the Max date of my FactTable ?

It is preferred also to doing this from backend if possible, means maybe from Power Query during Transform data.

Thanks,

[amitchandak]

@admin_xlsior , Create a column in the date table like

Date Type = SWITCH(TRUE(),'Date'[Date]=max(fact[date]),"Max Date"
,'Date'[Date]&"")

And select max date as default in slicer of page level filter

Refer :https://www.youtube.com/watch?v=hfn05preQYA

[admin_xlsior]

HI @amitchandak 

It's a good idea and sample. However if I want to put this Date dimensions as a "Between" or "Before" (the one with slider) how to filter it ?

I think I get your idea, so I make some ideas, is to add the column in Date dimensions to have MaxDate column, like this : 

Max date = MAX(Transactions[Transaction date])
 
So in that Date dimension, there is one column, all filled in with that same Maximum date of my Fact (Transactions)
But how to filter it ? Can we use the Advance Filtering and choose that Calculated column ?
 
Sorry, it might be out of your example, but I just need to make a better understanding of how this is play.

thanks

[v-shex-msft · Accepted solution]

Hi @admin_xlsior,

I'd like to suggest you create a measure formula with similar logic to check if the current date is large than or less than the static max date. Then you can use it on the visual level filter to exclude the records who over the maximum date.

Applying a measure filter in Power BI 

Regards,

Xiaoxin Sheng