How to apply different filters based on a time cutoff and apply it on same visuals

[padfootkk]

Hi everyone,

Now I'm facing a problem to apply different filters on the same visuals. Suppose I have two tables: Itemtable and Datetable, where Datetable contains date which includes month and year; An simple example of Itemtable is shown below. The two tables are connected by date.

Itemtable

Itemid	Area	Date
1	A	date
2	A	date
3	B	date
4	B	date
5	B	date
6	C	date
7	D	date

Say I have a column chart that shows the count of items as y-axis, month as x-axis. Now I need to filter the data under different areas based on a time cutoff. For example, before 05/2019, I need the count of items under area path A, B; After 05/2019 (including 05/2019), I need the count of items under area path A, B, C. Different filters 

Is it possible to filter like what I want?

[v-lid-msft · Accepted solution]

Hi @padfootkk ,

Firstly, we create two spreate calculated table to select the area filter

AeraSelectAfterDate = DISTINCT('Table'[Area])

AeraSelectBeforDate = DISTINCT('Table'[Area])

Then we use the following measure as the value field of column chart:

Count of items =
IF (
    MAX ( 'Table'[Date] ) < DATE ( 2019, 5, 1 ),
    CALCULATE (
        COUNT ( 'Table'[Itemid] ),
        FILTER ( 'Table', [Area] IN FILTERS ( AeraSelectBeforDate[Area] ) )
    ),
    CALCULATE (
        COUNT ( 'Table'[Itemid] ),
        FILTER ( 'Table', [Area] IN FILTERS ( AeraSelectAfterDate[Area] ) )
    )
)

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[padfootkk]

Thank you so much! This is exactly what I want 🙂

[amitchandak]

Try. in case of date is not coming from slicer

calculate(count(itemtable[item]),datetable[date]<date(2019,05,31))

calculate(count(itemtable[item]),datetable[date]>=date(2019,05,31))

Date coming from slicer

measure1 =
var _max_date = maxx(datetable,datetable[date])
return 
calculate(count(itemtable[item]),datetable[date]<_max_date))

measure2 =
var _max_date = maxx(datetable,datetable[date])
return 
calculate(count(itemtable[item]),datetable[date]>=_max_date)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601