Reply
Frequent Visitor
Posts: 2
Registered: ‎01-11-2019

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

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

Regular Visitor
Posts: 25
Registered: ‎10-29-2018

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

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
Super User
Posts: 3,748
Registered: ‎01-14-2017

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

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.