Forum Discussion

istvan_nagyracz's avatar
istvan_nagyracz
Regular Visitor
5 years ago
Solved

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

Date
DAX
Filter
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

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

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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's avatar
      istvan_nagyracz
      Regular Visitor
      5 years ago

      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's avatar
        CNENFRNL
        Community Champion
        5 years ago

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