Forum Discussion
Counting Most Recent Status
The goal is to count distinct ID the most recent status. I was able to get a measure that would solve this in most cases but realized that some transactions can have multiple status' on the same day. Ultimately, the expected output should have 1 distinct ID + the most recent status. Below are the following: Sample Data (Measure being used / Table 1 = Current Output example / Table 2 = Expected Output example)
Any advice on how to rework the current measure to only count the most recent status with only 1 distinct ID?
Sample PBIX > Sample PBIX Data
Measure
Pattern Count.OppID =
SUMX(
SUMMARIZE(
ALLSELECTED(Patterns),
Patterns[OPP_ID], "Date",MAX(Patterns[DateInserted1])
), IF(
[Date] >= CALCULATE(MIN(Patterns[DateInserted1]) )
&& [Date] <= CALCULATE(MAX(Patterns[DateInserted1]) )
,1, BLANK()
)
)
Table 1= Current Output Example
| OPP_ID | SUMMARY | OPP_STATUS | OPP_VALUE | DateInserted1 | |
| 697344 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 78.84 | Thursday, November 5, 2020 | |
| 697345 | Store Rollout: Shipped not Sold | In progress | 56.1 | Thursday, November 5, 2020 | |
| 697346 | Store Rollout: Shipped not Sold | New | 58.86 | Thursday, November 5, 2020 | |
| 697347 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 112.89 | Thursday, November 5, 2020 | |
| 697348 | Store Rollout: Shipped not Sold | In progress | 93.81 | Friday, November 6, 2020 | |
| 697348 | Store Rollout: Shipped not Sold | New | 93.81 | Thursday, November 5, 2020 | |
| 697349 | Store Rollout: Shipped not Sold | In progress | 84.33 | Thursday, November 5, 2020 | |
| 713012 | Store Rollout: Shipped not Sold | New | 81.34 | Friday, November 13, 2020 | |
| 713012 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 81.34 | Friday, November 13, 2020 | |
| 713013 | Store Rollout: Shipped not Sold | New | 89.41 | Friday, November 13, 2020 | |
| 713013 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 89.41 | Monday, November 23, 2020 | |
| 713014 | Store Rollout: Shipped not Sold | New | 78.07 | Friday, November 13, 2020 | |
| 713014 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 78.07 | Monday, November 16, 2020 | |
| 713015 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 97.95 | Friday, November 13, 2020 |
Table 2 = Expected Output Example
| OPP_ID | SUMMARY | OPP_STATUS | OPP_VALUE | DateInserted1 | |
| 697344 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 78.84 | Thursday, November 5, 2020 | |
| 697345 | Store Rollout: Shipped not Sold | In progress | 56.1 | Thursday, November 5, 2020 | |
| 697346 | Store Rollout: Shipped not Sold | New | 58.86 | Thursday, November 5, 2020 | |
| 697347 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 112.89 | Thursday, November 5, 2020 | |
| 697348 | Store Rollout: Shipped not Sold | In progress | 93.81 | Friday, November 6, 2020 | |
| 697349 | Store Rollout: Shipped not Sold | In progress | 84.33 | Thursday, November 5, 2020 | |
| 713012 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 81.34 | Friday, November 13, 2020 | |
| 713013 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 89.41 | Monday, November 23, 2020 | |
| 713014 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 78.07 | Monday, November 16, 2020 | |
| 713015 | Store Rollout: Shipped not Sold | Resolved - APS/Store | 97.95 | Friday, November 13, 2020 |
Any advice will be greatly appreciated!
- Anonymous5 years ago
I was able to correct the issue by changing the measure to reflect the date/time column. Funny how I spent so many hours trying to figure this out but after I posted this here and dwelling on it for 10 mins, I figured it out.
2 Replies
- AnonymousNot applicable
I was able to correct the issue by changing the measure to reflect the date/time column. Funny how I spent so many hours trying to figure this out but after I posted this here and dwelling on it for 10 mins, I figured it out.
- v-easonf-msftCommunity Support
Hi, Anonymous
It's pleasant that you can share your sloution to us.
Thanks for your sharing.Other community members will easily find the solution when they get the same issue.
Best Regards,
Community Support Team _ Eason