choose date range for a Date Table.

[PPStar]

Hello. 

I have a date table coming in from SQL. I ingest this Date table into my semantic Model.  The date table is quite big, as the date starts in 2015 and ends in 2030 (With lots of columns)

I want users to connect to this date table in Power BI Desktop - so they connect to the semantic model via a Live Connection. 

Optionally, if they want to add thier own data sources, they can create a composite model. 

Is there a way i can create a function in my Power BI Desktop which lets them choose a date range, so they only download data between a specified date range, e.g. 2023 to 2024

Note, they are connected to the data table via a Live Connection - so the question is, can they create a function to only a Live Connection / Direct query to limit the date range within a custom range. 

Alternativly, is there a better way to acheive this?. 

The aim is that the date table is managed by corporate IT that can be re-used by multiple team members. (so one team might need data from 2019 to 2020, other teams might want data from 2020-2023 etc). 

 Users can connect to to the date table, but shouldnt be able to make any changes to it. (Except the ability to limit the date range). 

I know if the date table was imported, we can create a filter. Can this be acheived via a function, if so how?

Thanks in advance. 

[v-jiewu-msft · Accepted solution]

Hi @PPStar ,

If I understand correctly, the issue is that you want to limit the date range within a custom range. Please try the following methods and check if they can solve your problem:

1.Go to the report, select the slicer visual.

2.Drag the date field from the date table into the Field area of the slicer.

Looking forward to your reply.

Best Regards,

Wisdom Wu

[ppm1]

This sounds like a great use for a Dataflow. Date tables and org hierarchy data are usually the first two dataflows a company needs.

Pat

[Idrissshatila]

Hello @PPStar ,

they can filter the date table in power query to show date between a certain range that they pick.

[PPStar]

They are connected to the data table via a Live Connection / Direct Query - They do not have access to Power Query to perform the filter?