Automation by using Date filters

[rj1120]

Hello Everyone,

I'm new to Power BI.I have some issue with date filters.Please help me..

I have one excel transfermation file.In this few sheets are there.By using these sheets,I'm creating different roports for last 4 weeeks and current week in the same dashboard.

For automation purpose,I'm using Edit Query in Power BI Desktop.In the power query I'm using date filters.When I apply filter for current week it will be applicable for last 4 weeks report also and vice versa  because I'm using same data for both reports.

How can I apply filters for the two reports seperately ??

Can any one give some solution for this.Thanks in advance.. 

[ankitpatira]

@rj1120 I am guessing you have 4 reports on 4 seperate pages. You can choose between page level filters and report level filters. Page level filters will apply to all visuals on that page only and report level will apply to all pages. That would be an easy way to apply filter to certain visuals or just one page.

[rj1120]

Hi Ankit,

Thanks for the sugesstion.

But i dont want to do that because with your solution if next time new week data comes and if refereshes the reports then i have to change the filters as well manually.

I just want to avoid manual work and automatically my reports should show four week and current week data based on date filter.

Please let me know if i am understanding anything wrong here. I suppose it should work like this.

Thanks 

Rajendra

[v-haibl-msft · Accepted solution]

@rj1120

In this scenario, I think you can create another two calculate tables, one only includes data for current week and another one only includes data for last four weeks. Below is a simple sample, hope it helps.

Assume that we have a simple table which includes all data from 5/1/2016 to 8/5/2016 as below. We can create a new WEEKNUM column with following formula.

WeekNum = WEEKNUM ( Table1[Date] )

Then we can use following formulas to create another two tables.

CurrentWeek = 
CALCULATETABLE (
    Table1,
    FILTER ( Table1, Table1[WeekNum] = MAX ( Table1[WeekNum] ) )
)

LastFourWeeks = 
CALCULATETABLE (
    Table1,
    FILTER (
        Table1,
        Table1[WeekNum] < MAX ( Table1[WeekNum] )
            && Table1[WeekNum]
                >= ( MAX ( Table1[WeekNum] ) - 4 )
    )
)

Now we can drag data from above two tables for the corresponding report. I’ve uploaded this Power BI file here in case you need it.

Best Regards,

Herbert

[rj1120]

For automation purpose,I'm using Edit Query in Power BI Desktop.In the power query I'm using date filters.When I apply filter for current week report it will be applicable for last 4 weeks report also and vice versa  because I'm using same data for both reports.

How can I apply filters for the two reports seperately where data comes from the same sheet/workbook/table??

Below is my screenshot.

My Query is somehow if we can apply automated filters to each report.

[samdthompson]

Hi, can you post some screen shots of the data?