Forum Discussion

rj1120's avatar
rj1120
Frequent Visitor
10 years ago
Solved

Automation by using Date filters

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 d...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    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

ankitpatira's avatar
ankitpatira
Community Champion
10 years ago

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's avatar
rj1120
Frequent Visitor
10 years ago

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.