Dynamic Default date in Date Slicer (No silly Text)

[Nitinmathur]

Hello All Gurus,

I am trying to show Vertical List date slicer which allows single selection and which should dynamically shows todays date as default selection along with all previous dates list. So after daily refresh, default selection changes to the date when it got refreshed and remaining dates shows to select as an option.

I have workarounds however it replaces Today's date with a text like "Today", "Current Date" or whatever text I can input. User wants to see actual date as default selection. But I am not able to write any DAX in new column which is used as Slicer to show actual date even though I have converted it to String format.  When any Text can be shown as default selection then why not Date converted into String (By Using Format or Convert function) can be shown as default selection??

It is a very basic user requirement. 

[Nitinmathur]

@OwenAuger  Thanks for the detailed explanation, Had issue updating to latest PBI desktop in my laptop so couldn't try TMDL option earlier.

I tried TMDL option today and it didn't work out as explained in your reply.

I kept getting error on Date Offset column as " Column [Date Secondary] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression."

Foe simplicity, I just kept 3 columns in the calendat table and for dynamic date to be updated in the selector I added another table (Table_Test)whose max date should come by default as slicer default value. So Date Offset is set 0 when Calendar table Secondary Date is matched with Max Date in the Table_Test else it will have 1:1 values from Secondary Date.

Here is Calendar Table/TMDL before:

Here is Calendar Table/TMDL after:

Please suggest what's wrong in these steps

[OwenAuger · Accepted solution]

Hi again @Nitinmathur 

No problem 🙂

It looks like the issue is a circular reference of sorts: if Date Offset is a calculated column that depends on Date Secondary (as in your example), the engine will not allow Date Secondary to have Group By Columns set to Date Offset.

There are many ways circular references can rear their head when defining calculated tables and columns, so I try to keep such definitions in Power Query or further upstream where possible.

A quick fix in your case would be to include Date Secondary in the Dim Calendar calculated table expression.

For example:

Calendar Dim = 
ADDCOLUMNS (
    CALENDAR ( "03/01/2025", MAX ( Test_Table[Date_Col] ) ),
    "Date Secondary", [Date],
    "Date Offset", IF ( [Date] = MAX ( Test_Table[Date_Col] ), 0, [Date] )
)

You should then be able to use TMDL View to update Date Secondary by adding:

relatedColumnDetails
    groupByColumn: 'Date Offset'

Your particular definition of Date Offset would ensure that selection of the "current date" always remains as a selection of the "current date" after refresh, but selection of any other dates remains as a selection of the displayed date.

Regards

[Nitinmathur]

This worked!

But I am bit curious that we are kind of trying hit and trial and gettin this to work for a very specific slicer type after going thorugh multiple steps. It should have been worked on a first place when we used calculated column of string type with text ("Today" etc) for dynamic value  Or Group by property should be made available in pbi , instead of going though TMDL or tabular editor.

[OwenAuger]

For this particular method, the first challenge is that we're using the Group By Columns property which is more-or-less undocumented in relation to Power BI by Microsoft (the SQLBI article is the one I always refer to), and requires an external tool or TMDL to update.

It's certainly not the only feature like this. Calculation group editing used to be similar and editing in Power BI Desktop is relatively new.

In my original reply the blog post I linked to had a PBIX attached but we needed further discussion to adapt that to your model.

Otherwise, it's the perennial challenge of solving these kinds of things via forum 😉

[Nitinmathur]

Hello OwenAuge,

Got another use case , user wants to see default date in the Start Date or End Date Filter silcer. when "Between" is used. Example User wants to restrict End Date default to max transaction date from the Fact table or Yesterday date by default in the End Date.

[OwenAuger]

Hi again @Nitinmathur 

Unfortunately I don't believe the "group by columns" method works in a way that can be applied to the endpoints of a "between" date slicer 😞

You could restrict the visible dates on the between slicer with a visual-level filter such as

Fact table nonempty = INT ( NOT ISEMPTY ( FactTable ) )

or you could use some sort of calculation group method like in this article but probably simpler:

https://www.sqlbi.com/articles/customizing-default-values-for-each-user-in-power-bi-reports/

[OwenAuger]

Hi @Nitinmathur 

This is unfortunately not a built-in feature for the core slicer visuals. However here are a couple of options:

1. Add a "Date Offset" column that is always 0 for the current date (on refresh) and set the Group By Columns property of Date to Date Offset. Power BI translates the filter into a filter on Date offset instead. This works fine with a vertical list slicer (but has some display issues with drop-down slicer).

See Solution 2 in this post.

2. Use the Preselected Slicer custom visual with an appropriate measure.

https://appsource.microsoft.com/en-us/product/power-bi-visuals/insiderscoop1611244107840.powerbi_cus...

I believe "default selection" is planned for the core slicer visuals at some stage, or at least has been hinted.

Do either of these methods work for you?

Regards

[Nitinmathur]

Thanks Owen, Could you please explain bit more about Option 1? where Can I see "Group By Columns property" of Date and set its value to Date Offset?

[OwenAuger]

@Nitinmathur  sure thing 🙂

Assuming you want the slicer to use a Date column from your Date table, here are the steps. After running some tests just now, it turns out that there is a bit more detail to add on top of the blog post linked above (since that concerned a "start of month" column in the Date table).

For now, I'll assume you have a 'Date' table with column 'Date'[Date], which is marked as a date table with 'Date'[Date] specified as the date column.

Steps

1. Add a copy of the Date column to the 'Date' table. I'll assume it's 'Date'[Date Secondary].
This is needed because 'Date'[Date] is normally set as the "key" column of the table (when marked as date table) which conflicts with setting the Group By Columns property.

2. Add a Date Offset column to the 'Date' table. This column can be configured however you like, but should map 1:1 to the Date Secondary column. I'll assume 'Date'[Date Offset] = 0 corresponds to the current date.

3. Use either Tabular Editor 2, Tabular Editor 3, or the TMDL View in Power BI Desktop to set the Group By Columns property of 'Date'[Date Secondary] to 'Date'[Date Offset].

(a) Using Tabular Editor 2:

1. External Tools > Tabular Editor 2
2. Select 'Date'[Date Secondary] column.
3. In Properties pane, find Options > Group By Columns and click ... on the right.
4. Add > Select Column > Date Offset > OK > OK
5. Save model changes back to Power BI Desktop (Ctrl-S).

(b) Using Tabular Editor 3:

1. External Tools > Tabular Editor 3
2. TOM Explorer > select 'Date'[Date Secondary] column.
3. In Properties pane, find Options > Group By Columns and click ... on the right.
4. Select Date[Date Offset] > OK.
5. Save model changes back to Power BI Desktop (Ctrl-S).

(c) Using TMDL View in Power BI Desktop (this is still in Preview)

1. Select TMDL View (navigation on left)
2. Either Drag Date table from Data pane to script area or right-click Date table > Script TMDL to > Script tab. This places the TMDL script for the 'Date' table in a script tab.
3. Find column 'Date Secondary' within the script and add the two lines of relatedColumnDetails code as shown below. Indentation is important for TMDL.
4. Click Apply > Message "Changes applied to the model." should appear.

Before:

		column 'Date Secondary'
			dataType: dateTime
			formatString: dd/mm/yyyy
			lineageTag: d837980d-4fa8-4335-989a-b2da7de9b339
			summarizeBy: none
			sourceColumn: Date Secondary

			annotation SummarizationSetBy = Automatic

			annotation UnderlyingDateTimeDataType = Date

After:

		column 'Date Secondary'
			dataType: dateTime
			formatString: dd/mm/yyyy
			lineageTag: d837980d-4fa8-4335-989a-b2da7de9b339
			summarizeBy: none
			sourceColumn: Date Secondary

			relatedColumnDetails
				groupByColumn: 'Date Offset'

			annotation SummarizationSetBy = Automatic

			annotation UnderlyingDateTimeDataType = Date

4. Now you can create a vertical list slicer in a report using 'Date'[Date Secondary]. If you select the date with Date Offset = 0, then the selection is internally bound to Date Offset rather than Date Secondary. When the model is refreshed and a new date has Date Offset = 0, that date will be selected. Selecting dates corresponding to other Date Offset values will behave in a similar way.

Notes:

• I'm guessing you would want to place a visual-level filter on a vertical list slicer. You could use the Date Offset column itself for this (e.g. -15 <= Date Offset <= 0) or a relative date filter.
• This Group By Columns method works best with vertical list slicers or other slicers where all options are visible. With drop-down slicers, the filter updates correctly when the Date Offset column changes, but the value displayed on the slicer will be "stuck" on the old value until you interact with the slicer.
• Hopefully updates to the core visuals make this whole method redundant soon!

I realise that was a bit of detail with the different options.

Let me know how you get on!

Owen 🙂