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]
    )
v-chuncz-msft's avatar
v-chuncz-msft
Community 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!