Benchmarking data and visual filters

[MarkDryden]

My scenario requires a dashboard to benchmark data against all "tenants" if they have sales data within that same year. 

I have a reference table called Tenants with TenantID, Smallest Year (or their first sale) and Largest Year (their last sale) (e.g. 890333, 2018, 2020 )

I then have the sales table called Sales with TenantID and the date of the sales (e.g. 890333, 05/11/2018)

The two tables are linked by TenantID

At present, I've got a measure that counts the total sales for all years - happy days. However, I need to perform a count where appropriate tenants operated within the year, e.g.

Tenant	2017	2018	2019	2020
A	108	86	156	58
B	0	0	7	42
C	0	400	300	0

Here, if I filter (using the visual filters):

• 2018 and 2019 I want to see Tenant A and C (but not B because it had no sales in 2018)
• 2019 I want to see Tenants A, B and C
• 2019 and 2020 I want to see Tenant A and B (but not C because it had no sales in 2020)

Now I thought I could do this by using a measure that could filter against the Smallest and Largest Year to return the appropriate TenantIDs, e.g.

All Sales (Benchmarked) =

VAR MinYear = YEAR((MIN(SalesData[Sales Creation Date])))

VAR MaxYear = YEAR((MAX(SalesData[Sales Creation Date])))

RETURN COUNTX( FILTER( SalesData , ( RELATED(Tenants[First Year]) <= MinYear && RELATED(Tenants[Last Year]) >= MaxYear ) ) , SalesData[TenantID])

But this doesn't work. If I hardcode the MinYear and MaxYear, it works but calculating the MIN and MAX doesn't. This makes me think that the Visual filters and how they are applied means it is using all the data when processing, then applying the Visual filter giving the appearance of working 🙄. Would really appreciate any insight into how to achieve what I'm after...

Thanks in advance...

[amitchandak]

@MarkDryden Try a measure like

measure =
var _cnt = countx(allselected(Table),DISTINCTCOUNT(Table[Year]))
return
sumx(filter(summarize(Table, Table[year],"_1",[sales],"_2",calculate([sales],allselected(Table[year]))),[_2]=_cnt),[_1])

[MarkDryden]

OK, Second time of writing this...

So I'm intrigued but have some questions.  I get that you are counting in the variable to determine the "range" (I think).  Then summarising across the [sales] data to effectively capture the same range - or just return everything?

This throws up a couple of questions...

1. In the SUMMARIZE you are using '[sales]' as a table when it requires a aggregate / scalar value / measure - what needs to go here?
2. Really unsure what you are getting from the CALCULATE as this needs an expression then filters?

Apologies for the questions and I really appreciate the reply, just trying to get things clear in my head, etc.

Thanks in advance...