FIlter multiple visuals multiple ways based on date selection

[LindaJonesCT]

I have a report with hundreds of visuals that need to be filtered 1 of 3 ways.  Of course each page requires a mix of the filters

Example: for FY2021-Q2 I need these filters

1. 2021-Q2
2. 2021-Q2, 2021-Q1, 2020-Q2
3. 2021-Q2, 2020-Q2, 2019-Q2, 2018-Q2

What I would love is a when I need to produce the Q3 report that I could choose 2021-Q3 and all the visuals would update accordingly.  Currently, it takes me over an hour to update the report - and there is no way I could publish the report for self-service.

[v-yangliu-msft · Accepted solution]

Hi  @LindaJonesCT ,

According to your description, I create this data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 = SUMMARIZE('Table','Table'[Year_Qtr])

2. Create page 2, page 3, page 4, use [Year_Qtr] of Table2 as the slicer, and use Sync Slicers to synchronize with the other three pages

3. Create measure.

2021-Q2：

Page2=
var _select=SELECTEDVALUE('Table 2'[Year_Qtr])
return
CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[Year_Qtr]=_select))

2021-Q2, 2021-Q1, 2020-Q2：

Page3 =
var _select=SELECTEDVALUE('Table 2'[Year_Qtr])
var _year=VALUE(LEFT(_select,4))
var _quater=VALUE(RIGHT(_select,1))
return
IF(
    MAX('Table'[Year_Qtr])=_select||
    AND(MAX('Table'[Year])=_year,MAX('Table'[Qtr])=_quater-1)||
    AND(MAX('Table'[Year])=_year-1,MAX('Table'[Qtr])=_quater)
    ,1,0)

2021-Q2, 2020-Q2, 2019-Q2, 2018-Q2：

Page4 =
var _select=SELECTEDVALUE('Table 2'[Year_Qtr])
var _year=VALUE(LEFT(_select,4))
var _quater=VALUE(RIGHT(_select,1))
return
IF(
    MAX('Table'[Year_Qtr])=_select||
    AND(MAX('Table'[Year])=_year-1,MAX('Table'[Qtr])=_quater) ||
    AND(MAX('Table'[Year])=_year-2,MAX('Table'[Qtr])=_quater) ||
    AND(MAX('Table'[Year])=_year-3,MAX('Table'[Qtr])=_quater)
    ,1,0)

4. Place the membrane created above in the specified Page, then place the measure in Filters, is=1, and select Apply filter.

5. Result.

Select the slicer in Page1 as 2020-1, and the results of the remaining pages are:

2021-Q2：

2021-Q2, 2021-Q1, 2020-Q2

2021-Q2, 2020-Q2, 2019-Q2, 2018-Q2

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LindaJonesCT]

Thank you.  This is exactly what I needed.  😀

[v-yangliu-msft]

Hi  @LindaJonesCT ,

It’s not very clear about your Example description. If you want to filter out the data you need based on the year and quarter, you can use a slicer

I create data from 2018.1.1-2021.12.1:

Here are the steps you can follow：

1. Create calculated column.

Year = YEAR('Table'[date])

Qtr = QUARTER('Table'[date])

Year_Qtr = 'Table'[Year]&"-"&'Table'[Qtr]

2. Place Year_Qtr in the slicer, select a certain quarter of a certain year to display the data in the date range, you can select multiple

3. Result.

Select 2021-Q2, 2021-Q1, 2020-Q2, the amount displayed is the amount of this time range.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LindaJonesCT]

Thank you Lui - I guess I was not clear enough.

I have a date table that I am using for my filters and I am filtering as you suggested.  The issue is I can not use a page filter as 3 types of filter criteria need to be used

I am using visual filters.

When I need to change the filters for the next reporting period I need to change over 100 filters.  That is the issue I am trying to solve for.

(It is not so much a problem for me - but unacceptable for the end users.  

[amitchandak]

@LindaJonesCT , Not very clear, what data format you have. If you have data at date level , Time intelligence with date table and then you can travel previous quarters. If not you need to have a separate qtr year table YYYYQQ and have rank on that. and use that to travel

example

with TI

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

Without TI

New column in Date/Qtr table

Qtr Rank = RANKX(all('Date'),'Date'[Year Qtr],,ASC,Dense)

Measures

This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

2nd Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-2))

4th Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-4))

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

[LindaJonesCT]

I like your thoughts with the QTR rank column.  I am just not sure how to make this work.  Here is a picture of a small section of the report.  All visuals are filtered in the QTR (FY 2021-Q2) except for the bar chart.  

If I filter the page by FY 2021-Q2 - how do I get the bar chart to work?