Forum Discussion
Solved
Creating a Dynamic Date Filter
Hi there! I’m trying to create a filter on a visual that will tell me the value of opportunities in my pipeline on any given date. Whenever I make a change to the opportunity, it gets added to my d...
You may try the measure below.
Measure = VAR d = MAX ( 'Table'[Date Last Changed] ) RETURN SUMX ( SUMMARIZE ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date Last Changed] <= d ), 'Table'[Opportunity ID], "V", SUMX ( TOPN ( 1, 'Table', 'Table'[Date Last Changed], DESC ), 'Table'[Value] ) ), [V] )
Thank you so much Greg_Deckler!
A consultant helped me with the formula to find our current latest value - she made it in a new column with this formula:
Latest Value = IF('Table'[DateChanged]=CALCULATE(MAX('Table'[DateChanged]), FILTER('Table', EARLIER('Table'[Opportunity ID]) = 'Table'[Opportunity ID])),CALCULATE(MAX('Table'[Value]), FILTER('Table','Table'[Opportunity ID]=EARLIER('Table'[Opportunity ID]) && 'Table'[DateChanged] = CALCULATE(MAX('Table'[DateChanged]), FILTER('Table', EARLIER('Table'[Opportunity ID]) = 'Table'[Opportunity ID])))),0)
I tried creating a measure with your suggestion and I believe it would look something like the below but obviously, this isn't correct because it doesn't work... :( Thoughts?
Measure =
VAR __maxDate = MAX('Table'[DateChanged])
VAR __table = FILTER(ALL('Table'),[DateChanged] <= __maxDate)
return
IF('Table'[DateChanged]=CALCULATE(MAX('Table'[DateChanged]), FILTER('Table', EARLIER('Table'[Opportunity ID]) = 'Table'[Opportunity ID])),CALCULATE(MAX('Table'[Value]), FILTER('Table','Table'[Opportunity ID]=EARLIER('Table'[Opportunity ID]) && 'Table'[DateChanged] = CALCULATE(MAX('Table'[DateChanged]), FILTER('Table', EARLIER('Table'[Opportunity ID]) = 'Table'[Opportunity ID])))),0)Thank you so much!!
v-chuncz-msft
Community Support
Community Support
You may try the measure below.
Measure =
VAR d =
MAX ( 'Table'[Date Last Changed] )
RETURN
SUMX (
SUMMARIZE (
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date Last Changed] <= d ),
'Table'[Opportunity ID],
"V", SUMX ( TOPN ( 1, 'Table', 'Table'[Date Last Changed], DESC ), 'Table'[Value] )
),
[V]
)
Thank you v-chuncz-msft ! I can tell this is getting me closer to the result I'm looking for! I'm going to try tinkering with it and I will report back if I get it!