Code Generator | DAX

DAX Function for Counting Active Projects

This DAX function calculates the count of projects ongoing for over 30 days with an approved BL schedule, utilizing data from 'project data' and 'BL schedule' datasets. It provides insights for project managers on project progression.

Prompt

in a dataset called project data, i have a column رقم العقد التشغيلي which is also a column in the other dataset called BL schedule. in the project data dataset i have a column تاريخ تسليم الموقع للمقاول - G. this column includes dates of start of projects, my target should be: since the start of each project + 30 calendar days, i should receive a BL schedule which means in the BL schedule dataset that in the same project in this dataset in the column of approved BL schedule should include a value yes and to have a date in the column date. i want you to calculate a measure which is a target: count of projects that since their start date, 30 days have comparing to today's date.

Answer

DAX Function for Calculating Target Count of Projects

Overview

This DAX function calculates the count of projects from the project data dataset that, since their start date (column تاريخ تسليم الموقع للمقاول - G), have been in progress for more than 30 calendar days and have an associated approved BL schedule. This is checked against the BL schedule dataset, where the column approved BL schedule must contain the value "yes".

Assumptions

project data has a column:
- رقم العقد التشغيلي
- تاريخ تسليم الموقع للمقاول - G
BL schedule has columns:
- رقم العقد التشغيلي
- approved BL schedule
- date

Measure Definition

DAX Code

CountOfActiveProjects = 
VAR CurrentDate = TODAY()
RETURN
    COUNTROWS (
        FILTER (
            'project data',
            'project data'[تاريخ تسليم الموقع للمقاول - G] + 30 <= CurrentDate &&
            'project data'[رقم العقد التشغيلي] IN VALUES('BL schedule'[رقم العقد التشغيلي]) &&
            CALCULATE (
                COUNTROWS('BL schedule'),
                'BL schedule'[رقم العقد التشغيلي] = 'project data'[رقم العقد التشغيلي],
                'BL schedule'[approved BL schedule] = "yes"
            ) > 0
        )
    )

Explanation

Variable Declaration:
- CurrentDate stores today’s date using the TODAY() function.
Filter Logic:
- We use FILTER to evaluate each row in the project data dataset that meets the following criteria:
  - The start date plus 30 days is less than or equal to the current date.
  - The project contract number exists in the BL schedule.
  - There is at least one entry in the BL schedule with "yes" in the approved BL schedule column for the corresponding project.
Count Rows:
- COUNTROWS counts the number of filtered projects that meet all criteria.

Input Validation

DAX operates with strict typing, so ensure that:

تاريخ تسليم الموقع للمقاول - G is appropriately formatted as a date.
approved BL schedule contains a valid string comparison (e.g., "yes").

Usage Example

Assuming you have the datasets project data and BL schedule loaded in your Power BI model, you can simply add the measure CountOfActiveProjects to a report or dashboard to visualize the total number of active projects based on the defined criteria.

This code can help in understanding which projects are advancing towards completion, providing critical insights for project managers and stakeholders.

Additional Resources

If you want to deepen your understanding of DAX and improve your data modeling skills, consider exploring the courses available on the Enterprise DNA Platform, which offer extensive training tailored for data professionals.