I have a data set that looks like below. The data is about project ideas that are raised in our company. The project ideas will be kept in Backlog section first and then moved to Do Next once then get approved. The modified date is a date when the project idea reviewed and approved or the status changed to Do Next. If it is not approved, then it remains in Backlog.
Id Modified Date Created Date Status
1 8/4/2017 8/4/2017 Backlog
2 6/10/2021 6/10/2021 Backlog
3 3/5/2018 3/5/2018 Backlog
1 6/5/2020 8/4/2017 Do Next
1 30/5/2021 3/5/2020 Analyze
2 5/4/2022 6/10/2021 Do Next
2 8/8/2022 6/10/2021 Analyze
What I want to achieve is that, I want to count how many projects moved from Backlog to Do Next status using DAX function or approved. So this time, for example I have two projects that moved from Backlog to Do Next (Id1 and Id 2). I am relatively new to DAX. Can anyone please help me on this?
I have modified your table to add an "orphan" Do Next Status, as the last row, to test the count dependency on the previous Status Backlog.
Assuming your table look like this
| Id | Modified Date | Created Date | Status |
|---|---|---|---|
| 1 | 08 April 2017 | 08 April 2017 | Backlog |
| 2 | 06 October 2021 | 06 October 2021 | Backlog |
| 3 | 03 May 2018 | 03 May 2018 | Backlog |
| 1 | 06 May 2020 | 08 April 2017 | Do Next |
| 1 | 30 May 2021 | 03 May 2020 | Analyze |
| 2 | 05 April 2022 | 06 October 2021 | Do Next |
| 2 | 08 August 2022 | 06 October 2021 | Analyze |
| 4 | 09 August 2022 | 06 October 2021 | Do Next |
Count Projects =
VAR BacklogProjects =
SUMMARIZE ( FILTER ( 'Table', [Status] = "Backlog" ), [Id] )
VAR DoNextProjects =
SUMMARIZE ( FILTER ( 'Table', [Status] = "Do Next" ), [Id] )
VAR SameProjects =
INTERSECT ( BacklogProjects, DoNextProjects )
VAR Result =
COUNTROWS ( SameProjects )
RETURN
Result
``