cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
istvan_nagyracz
Regular Visitor

Create a filtered table based on a date filter

Dear Everybody,

 

I have a sales pipeline table in the structure below.

DateDeal nameDeal stage
2021-03-01appleFirst
2021-03-13appleSecond
2021-03-14appleThird
2021-05-01bananaThird

 

Based on this, I want to create a filtered table that is filtered by a date slicer. It also contains the most recent entry for each deal. So if the filter is set to 2021-03-12 - 2021-04-30 in the report, I would like to get the table below from the above.

DateDeal nameDeal stage
2021-03-14appleThird

 

What is the solution?

 

Thank you in advance for your help.

 

Have a nice weekend,
Istvan

1 ACCEPTED SOLUTION

@istvan_nagyracz , a separate date table is enough for a solution to your question,

Screenshot 2021-03-15 033519.png

_ = 
VAR __dt = MAX ( Deal[Input date] )
RETURN
    IF (
        __dt >= MAX ( Dates[Date] )
            && __dt = CALCULATE ( MAX ( Deal[Input date] ), ALLEXCEPT ( Deal, Deal[Deal Name] ) ),
        ""
    )

Screenshot 2021-03-15 033725.png

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

@istvan_nagyracz , as mentioned, the expected table results from a date filtering; thus first you need to create a date table and then you might want to try a measure,

_ =
IF (
    MAX ( Deal[Date] ) = CALCULATE ( MAX ( Deal[Date] ), ALLEXCEPT ( Deal, DATES[Date] ) ),
    ""
)

Screenshot 2021-03-13 043936.png

Dear @CNENFRNL,

 

Thank you very much for your help. It’s a very tricky solution, but unfortunately it’s not quite the result I want.

 

In another example, perhaps I can better describe what I would need.

 

Given this table:

Input date Deal name Deal stage
2021.02.05 a Offer sent
2021.02.05 b Offer sent
2021.02.05 d Offer sent
2021.02.11 e Offer sent
2021.02.11 f Contract sent
2021.02.12 d Closed won
2021.02.15 e Closed won
2021.02.16 g Qulaified to buy
2021.02.16 a Closed won
2021.02.16 b Closed won
2021.02.16 c Closed won
2021.02.16 h Decision maker bought-in
2021.02.16 i Closed won

 

If I filter from this to the Input date field (2021-02-06), I get the following:

Input date Deal name Deal stage
2021.02.11 e Offer sent
2021.02.11 f Contract sent
2021.02.12 d Closed won
2021.02.15 e Closed won
2021.02.16 g Qulaified to buy
2021.02.16 a Closed won
2021.02.16 b Closed won
2021.02.16 c Closed won
2021.02.16 h Decision maker bought-in
2021.02.16 i Closed won

 

And from that, I would need the most recent date for each deal, but this latest date is different for each deal:

Input date Deal name Deal stage
2021.02.11 f Contract sent
2021.02.12 d Closed won
2021.02.15 e Closed won
2021.02.16 g Qulaified to buy
2021.02.16 a Closed won
2021.02.16 b Closed won
2021.02.16 c Closed won
2021.02.16 h Decision maker bought-in
2021.02.16 i Closed won

 

Thanks in advance if you can help with that too,

Istvan

@istvan_nagyracz , a separate date table is enough for a solution to your question,

Screenshot 2021-03-15 033519.png

_ = 
VAR __dt = MAX ( Deal[Input date] )
RETURN
    IF (
        __dt >= MAX ( Dates[Date] )
            && __dt = CALCULATE ( MAX ( Deal[Input date] ), ALLEXCEPT ( Deal, Deal[Deal Name] ) ),
        ""
    )

Screenshot 2021-03-15 033725.png

View solution in original post

Thanks, this is almost a good solution. I changed it so much that I linked the date and deal tables and removed the condition __dt> = MAX (Dates [Date]) from the measure formula.

 

I am grateful for your help, have a nice day,
Istvan

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.