Adding a date range field

[JoshK]

I'm very new to Power BI so am not familiar with all functionality...

I'm trying to set up a report where I can filter all fields by an annual date range without depending on when transactions have taken place...

e.g. All sales Jan - June 2019. I would want to see this so that if there were no sales in February or March for example, these columns would appear on the sheet but be left blank. Whereas at the moment I can only select 'sale date' or 'start date' etc. as this is all that is available from the data source in my fields. In doing this I would only have columns for January, April, May, June.

I am picturing some kind of additional 'calendar field' that I can create and set date perameters with that is not dependent on the dates I already have available in the data source.

[v-frfei-msft · Accepted solution]

Hi @JoshK,

The connection type is Import in your scenario. Here we cannot create calculated table. You can only model your data in your data source side. Please refer to the online document to check the limitations of Direct Query.

https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

Regards,

Frank

[v-frfei-msft]

Hi @JoshK,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,

Frank

[v-frfei-msft]

Hi @JoshK,

Please check the following steps.

1. Create a CALENDAR table.

Table = CALENDARAUTO()

2. Create relationship between calendar table and the fact table.

3. Create a table visual as below , please notice here we should enable the option "show items with no data".

For more details, please check the pbix as attached.

Regards,

Frank

[JoshK]

Thanks for your reply.

This makes sense in principle but 'New Table' is greyed out and unavailable...