Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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] ) ),
""
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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] ) ),
""
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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,
_ =
VAR __dt = MAX ( Deal[Input date] )
RETURN
IF (
__dt >= MAX ( Dates[Date] )
&& __dt = CALCULATE ( MAX ( Deal[Input date] ), ALLEXCEPT ( Deal, Deal[Deal Name] ) ),
""
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |