Forum Discussion
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 data set with the ID, value, and date I changed it, so my data looks like this:
Opportunity ID | Value | Date Last Changed |
AA | $100 | 6/30/19 |
CC | $50 | 5/30/19 |
AA | $75 | 4/30/19 |
BB | $0 | 4/30/19 |
BB | $100 | 3/30/19 |
DD | $200 | 3/30/19 |
This means that on any given date, the total value of my pipeline was:
Date | Total Value |
3/30/19 | $300 |
4/30/19 | $275 |
5/30/19 | $325 |
6/30/19 | $350 |
So, for example, on 3/30 I had two opportunities totaling $300, but on 4/30, one of those opportunities was zeroed out and another opportunity was added, so the total for that date would be $275, etc.
I am all set will getting the current (6/30) value of my pipeline that only pulls the value from the latest date changed, but I'd like to be able to go back in time and see what the value was on any given day.
I think I need to create some kind of dynamic filter that will tell me the total value based on the most recent date according to the date I set in my slicer (and ignoring data that was entered after that date), but I am totally stuck on how to do this. I've tried a lot of ideas I've seen in the forum, but can't quite get it to work. Any ideas?
Thank you so much for helping out a beginner!
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] )
4 Replies
Use the same logic that you already have. You can either use a separate date table for your slicer or utilize the ALL function. So, essentially, something like:
Measure = VAR __maxDate = MAX('Table'[Date]) VAR __table = FILTER(ALL('Table'),[Date] <= __maxDate) ...After that it should essentially the same logic that you have currently.
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!!
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] )
