Current period Filter should filter previous year Data

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.

Need Help

v-chuncz-msft
7 years ago
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

3 Replies

v-chuncz-msft
Community Support
7 years ago
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-date/m-p/588265#M278953

https://community.powerbi.com/t5/Desktop/i-have-two-dates-using-measures-and-want-to-set-these-dates-to/m-p/587373#M278445
- Charu
  Post Patron
  7 years ago
  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
    Community Support
    7 years ago
    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

Forum Discussion

Current period Filter should filter previous year Data

3 Replies

Related content

Previous Period measure

MTD FOR PREVIOUS PERIOD

Based on Dynamic period selection previous period value is incorrect

Previous period

Matrix period showing incorrect total when using previous year measure