Count the IDs accumulated

[ErickMontiel]

Hello everyone,

Could you please help me with the following?

The following table has an ID and finish date when one ID wasn't finish the closed date is blank. Now I want to count the lines that are in blank in the previous days according to the finish date like the example on the table:

[Ashish_Mathur]

Hi,

Write this calculated column formula

Column = COALESCE(CALCULATE(COUNTBLANK(Data[Closed Date]),FILTER(Data,Data[Finish Date]<EARLIER(Data[Finish Date]))),0)

Hope this helps.

[AnalyticsWizard]

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! 😊

[ErickMontiel]

Hello, thank you for the help.

I put the formula and it calculates the blank lines but for the same date, not the blank lines for previous dates. Could you help me again?

[gmsamborn]

Hi @ErickMontiel 

The line inside FILTER that compares 

MyTable[Finish Date] = CurrentFinishDate

should be

MyTable[Finish Date] <= CurrentFinishDate

or

MyTable[Finish Date] < CurrentFinishDate

Let me know how this goes.