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

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's avatar
    v-haibl-msft
    Microsoft Employee
    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