Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
phay
Regular Visitor

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!

 

 

1 ACCEPTED SOLUTION

@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]
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!

@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]
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.