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.
I have table as shown below and i need to find date for a given Region which has backlog as positive number after the last record which has negative backlog for that particular region.
Region | Date | Backlog |
A | 5/1/2019 | 10 |
A | 5/2/2019 | -1 |
A | 5/3/2019 | 100 |
A | 5/6/2019 | -2 |
A | 5/8/2019 | 200 |
A | 5/9/2019 | 100 |
B | 5/1/2019 | 20 |
B | 5/2/2019 | -10 |
B | 5/3/2019 | 30 |
B | 5/6/2019 | 40 |
B | 5/8/2019 | 30 |
B | 5/9/2019 | 40 |
Result Needed:
Region | Target Date |
A | 5/8/2019 |
B | 5/3/2019 |
How to accomplish it?
Solved! Go to Solution.
This mEASURE works with your sample data
Measure = VAR LastNegative = MINX ( TOPN ( 1, FILTER ( Table1, [Backlog] < 0 ), [Date], DESC ), [Date] ) RETURN MINX ( TOPN ( 1, FILTER ( Table1, [Backlog] >= 0 && [Date] > LastNegative ), [Date], ASC ), [Date] )
This mEASURE works with your sample data
Measure = VAR LastNegative = MINX ( TOPN ( 1, FILTER ( Table1, [Backlog] < 0 ), [Date], DESC ), [Date] ) RETURN MINX ( TOPN ( 1, FILTER ( Table1, [Backlog] >= 0 && [Date] > LastNegative ), [Date], ASC ), [Date] )
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |