Forum Discussion

phay's avatar
phay
Regular Visitor
7 years ago
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...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    phay 

     

    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]
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

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.

phay's avatar
phay
Regular Visitor
7 years ago

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's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    phay 

     

    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]
    )
    • phay's avatar
      phay
      Regular Visitor
      7 years ago

      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!