Create a filtered table based on a date filter

[istvan_nagyracz]

Dear Everybody,

I have a sales pipeline table in the structure below.

Date	Deal name	Deal stage
2021-03-01	apple	First
2021-03-13	apple	Second
2021-03-14	apple	Third
2021-05-01	banana	Third

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.

Date	Deal name	Deal stage
2021-03-14	apple	Third

What is the solution?

Thank you in advance for your help.

Have a nice weekend,
Istvan

[CNENFRNL]

@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] ) ),
    ""
)

[istvan_nagyracz]

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

[CNENFRNL · Accepted solution]

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

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

[istvan_nagyracz]

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