Count the IDs accumulated

Certainly! Let’s break down the task step by step:

1. Understanding the Problem:

   • You have a table with columns: ID, Finish Date, and Closed Date.
   • When an ID is not finished (Closed Date is blank), you want to count the number of lines (rows) with blank Closed Dates that occurred before each respective Finish Date.

2. Approach:

   • We’ll create a new column called Accumulated that represents the count of blank Closed Dates before each Finish Date.
   • For each row, we’ll calculate this accumulated count based on the Finish Date.

3. Solution:

   • Let’s assume your table is named MyTable.
   • We’ll use DAX (Data Analysis Expressions) to create the new column.

4. DAX Formula:

   • In Power BI Desktop, go to the Model View.
   • Right-click on your table (e.g., MyTable) and select New Column.
   • Enter the following DAX formula:

     Accumulated =
     VAR CurrentFinishDate = MyTable[Finish Date]
     RETURN
         CALCULATE(
             COUNTROWS(MyTable),
             FILTER(
                 MyTable,
                 MyTable[Finish Date] = CurrentFinishDate &&
                 ISBLANK(MyTable[Closed Date])
             )
         )

   • This formula calculates the count of rows where the Finish Date matches the current row’s Finish Date and the Closed Date is blank.

5. Explanation:

   • We use a VAR to store the current row’s Finish Date.
   • The CALCULATE function counts rows based on the specified conditions.
   • The FILTER function filters the table to include only rows with the same Finish Date and blank Closed Date.

6. Result:

   • Your new column Accumulated will show the count of blank Closed Dates before each Finish Date.

Here’s how the first few rows of your updated table might look:

ID Finish Date Closed Date Accumulated

1	11/23/2023	11/23/2023	0
2	11/23/2023	Blank	0
3	11/23/2023	11/23/2023	0
4	11/24/2023	11/24/2023	1
…	…	…	…

Remember to adjust the table and column names according to your actual data. If you encounter any issues or need further assistance, feel free to ask! 😊