Dynamic Filtering at PowerBI Report level

[Anonymous]

I have a report with 4 visualizations and a few cards that is currently displaying all of the data. I'd like to filter the data (and thus the visualizations) using some logic that uses the slicer value.

I have a planning table with lots of dates but here's a sample:

FiscalYear | ReportDate  |  Status   | 
2017       | 01/4/2018   | Open      | 
2018       |             | Open      | 
2017       |             | Closed    |
2016       | 01/10/2016  | Complete  | 
2016       | 01/8/2017   | Complete  | 

The slicer values are years, which are fed into some logic to effectively define a new table (or flag the existing table), e.g.

Slicer Value = 2017 Include anything reported on in 2017 or still open:

FiscalYear | ReportDate  |  Status   | 
2017       |             | Closed    |
2016       | 01/8/2017   | Complete  | 

Slicer Value = 2018 Include anything reported on in 2018 or still open:

FiscalYear | ReportDate  |  Status   | 
2017       | 01/4/2018   | Open      | 
2018       |             | Open      | 

The logic isn't really important, beyond knowing that some records could pop up in both years. I'm just wondering what is the best strategy for doing this?

I could filter the data by my logic in each measure but this is very clunky. And I can't use a calculated column as it's only done once and I said, some data will span multiple years..

I also have a date table with every date from 2003 to 2035..

Thanks

[v-jiascu-msft]

Hi @Anonymous,

Did it help? Can you mark it as an answer?

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Just see from your data here, it could be easy with a calculated column. You can check out the demo in the attachment. Can you share sample please?

Column =
IF ( ISBLANK ( [ReportDate] ), [FiscalYear], YEAR ( [ReportDate] ) )

Best Regards,

Dale