Current period Filter should filter previous year Data

[Charu]

Dear All,

Any of you please help me out to achieve the below scenario.

Example:I have 3 tables

1.SalesTable --SalesDate,SalesCost,Items are the fields.

In SalesTable,it has 2017,2018,2019,etc records.

2.PeriodTable--StartDate and EndDate are the fields.It only contains current period

Like Start_date:Jan-1-2019 and END_DATE:Jan-31-2019

3.Date Table--Date is the field which has 2017 to till dates

Scenario1:

a)In a report,Basically I need the Date filter that should display the from date and to_Date of the current period from the periodDate.

I tried the below DAX

NewDateTable=CALENDAR ( min('PeriodTable'[START_DATE]), max('PeriodTable'[END_DATE]) )

b) created relationship between SalesDate from SalesTable and Date from NewDateTable.And using the Date field in the Date Filter to display the current period of periodTable.

As I already said Salesdata contains 2017,2018,2019  record when I select the 2018 date from the filter,it's not working because those 2017,2018 dates are not there in the period table.

How to achieve this?

Scenario 2:

When I use the Date field as a filter from DateTable and created the relationship between date table's Date field and SalesTable's salesDate field,definetly I can able to see the salesdata for previous years as well,Since Date Table has all the periods (2017,2018,2019,etc).

But by default I need the current period to be displayed on the Date Filter.Even If I pick the previous year then I should see the Sales Datas.

Could you please help me to get the solution either for scenario 1 or scenario 2?

Basically I need is by default  the current period to be displayed on the Date Filter and Even If I pick the previous year then I should see those Sales Datas.

Thanks in advance.

[v-chuncz-msft]

@Charu,

You may take a look at the following posts.

• https://community.powerbi.com/t5/Desktop/Use-a-date-slicer-to-filter-on-a-period-instead-of-a-single...
• https://community.powerbi.com/t5/Desktop/i-have-two-dates-using-measures-and-want-to-set-these-dates...

[Charu]

Hi @v-chuncz-msft

Thanks for your post.Unfortunatley both the blogs not helps for my scenarios.

I have around 5 tables like(table1,table2,table2,etc) and separately one period table I need the Date filter where user should select the date to see the data.

my problem is in period table it will only contain the current month start date and end date.So if i create relationship between 5 tables i couldn't see the previous months data because in period table it has only 1-Feb-2019 and 28-Feb-2019.

But I need the Date filter as like below.By default from and To date must show the current period 

Even if i pick 2018 date Data should filter.

[v-chuncz-msft · Accepted solution]

@Charu,

Take a good look at this discussion.

http://community.powerbi.com/t5/Desktop/Relational-Date-Filter-By-Other-Date-How-To/td-p/298373