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. Date Deal name Deal stage 2021-03-01 apple First 2021-03-13 apple Second 2021-03-14 apple Third 2021-...
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] ) ),
        ""
    )

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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] ) ),
        ""
    )

      • istvan_nagyracz's avatar
        istvan_nagyracz
        Regular Visitor
        5 years ago

        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