Dynamic measure to display results for current year and previous 2 years

[Anonymous]

Hi, 

I have a measure which works fine but I want it to display the results dynamically for current year and last 2 years instead of me manually selecting the years 

Is it possible?

Heres my current measure:

1. DistinctAccount = DISTINCTCOUNT('AccountTable'[AccountId])

2. AccountMembers= CALCULATE(
[DistinctAccount],
FILTER(
ALLSELECTED('Calendar'[Date]),
('Calendar'[Date] <= MAX('Calendar'[Date]) 
))
,FILTER(ALLSELECTED(Identifier[Termination Year]),(Identifier[Termination Year]>SELECTEDVALUE('Calendar'[Year])))

Please let me know

Thanks

[Ashish_Mathur]

Hi,

Not sure of what you want but try this - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.

[sturlaws · Accepted solution]

Hi,

not quite sure about what you want to achive, but two things you could try.

In you calendar table, add a column which is 1 if the date is within the date range you define, 0 if it is outside of this range. Than add this column to the filter of your visual, and set it to filter on 1.

or

Create a measure like this

accounts =
VAR t =
CALCULATE ( YEAR ( MAX ( table1[dato] ) ); ALL ( table1 ) ) - 2
RETURN
IF ( YEAR ( MIN ( Table1[dato] ) ) >= t; distinctcount( Table1[accountID] ); BLANK () )

regards,

Sturla