Date Slicer with seperate date table

[SPLeon]

Hi,

I recently started with Power Bi. I made a report in Desktop that presents some sale and buy information.

On the page I added a date slicer which can filter the date. Based on the date I want to show how many sells and how many buys the customer has this period. 

So I created a date table with the dates between the start of this year and the end of 2028 (so 10 year).
From this table I created a relationship between the date of this table and the buy date (buy table) and sold date (sell table).

Everything works fine. I can filter and data is displayed correctly. However within my slicer I have a range of 10 year. 

What I want is that the slicer only can filter the dates which have a value in the buy or sell table. So that should be max till today.

Is there a way how I can achieve this? for example reduce the slicer to max today, or maybe I should modify the date table to have only dates till today (dynamically)?

[themistoklis · Accepted solution]

@SPLeon

The workstream on this link will definitely help you to do this.

Make sure on Power Query to use the following formula on the 'Invoke Function' Step.

Source(#date(2017, 01, 01), Number.From(DateTime.Date(DateTime.LocalNow()) - #date(2017, 01, 01))+1, #duration(1, 0, 0, 0))

You can get the custom calendar from this link.

Simply copy and paste the Calendar table (Power Query Editor) from the Calendar PowerBI workspace to your PowerBI workspace

[SPLeon]

I found another solution. It is a template which contains a date table.

Here you can specifiy the start date. The end date is the today's date. 

Many thanks.

[v-piga-msft]

Hi @SPLeon,

It seems that you have found the solution, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry