cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joshuar Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
sturlaws Regular Visitor
Regular Visitor

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
2 REPLIES 2
sturlaws Regular Visitor
Regular Visitor

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
Super User

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.