Measure to count the number of projects that are active by month

[Castle85]

Hi all,

I have a set of data that tracks 'jobs' there is a field for 'Date' which is the date the job enters the queue and a field for 'Due Date' in the same row, this is the due date until the job is 'Completed' where the 'Due Date' field becomes the completion date. I have data that goes back to Jan 2021 and am looking to create a measure that determines per month, a count of how many active requests there were i.e. a count of the number of rows where the value in my table is between the 'Date' and 'Due Date' a number of requests will span multiple months. I have tried the below and the numbers are way off it's giving a count in the hundreds where the number of active requests per month are typically ~ in the 30s/40s. I wonder if I have to divide by number of days in a month or something but alas any help appreciated!

Active =

SUMX (

    'Tracking',

    CALCULATE (

        COUNTROWS ( 'Tracking' ),

        FILTER (

            'Tracking',

            [Date] >= 'Tracking'[Date]

                && [Date] <= 'Tracking'[Due Date].[Date]

        )

    )

)

[amitchandak · Accepted solution]

@Castle85 , Refer to the approach used for Active Employee in Blog or files attached

Power BI: HR Analytics - Employees as on Date: https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[Anonymous · Accepted solution]

Hi @Castle85 ,

To calculate the number of active requests per month based on the given conditions, you need to create a calendar table and then use that table to calculate the number of active requests per month.
Refer to the following code:

1. create calendar table:

Calendar = CALENDAR(MIN('Tracking'[Date]), MAX('Tracking'[Due Date]))

2. modify your dax:

Active  = 
CALCULATE (
    COUNTROWS('Tracking'),
    FILTER (
        'Calendar',
        'Calendar'[Date] >= 'Tracking'[Date]
        && 'Calendar'[Date] <= 'Tracking'[Due Date].[Date]
    )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Castle85]

Thanks for your help but this unfortunately gives an error;

Any help appreciated with this one!

[amitchandak · Accepted solution]

@Castle85 , Refer to the approach used for Active Employee in Blog or files attached

Power BI: HR Analytics - Employees as on Date: https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU