Checking condition for a data range

[Dadoge]

Hello,

I'm dealing with a scenario where I have a table named "Table 1" with project ID, start date, and end date columns. What I aim to achieve is to count ongoing projects for each month within a list of dates between 2020 and 2023 (that doesnt exist).

For example, if Project ID 100 has a start date of 01/01/2020 and an end date of 01/04/2020, I want to count it as an ongoing project for the months 01/2020, 02/2020, 03/2020, and 04/2020.

Thanks in advance

[123abc · Accepted solution]

Create a Calendar Table

Create a new table in Power BI that contains a list of dates covering the range from 2020 to 2023. You can use the "CALENDAR" function in DAX to generate a date table. For example:

CalendarTable = CALENDAR(DATE(2020, 1, 1), DATE(2023, 12, 31))

Step 2: Relate Calendar Table with Project Table

Establish a relationship between the calendar table and your "Table 1" based on the date columns (start date and end date). You may need to create two relationships - one for the start date and another for the end date.

Step 3: Create Calculated Columns

Create a calculated column in the "Table 1" to mark the ongoing projects for each month. You can use the following DAX formula:

IsOngoing =
IF(
Table1[StartDate] <= MAX(CalendarTable[Date]) &&
Table1[EndDate] >= MIN(CalendarTable[Date]),
1,
0
)

This formula checks whether the start date of the project is before or on the current date in the calendar table and whether the end date is after or on the current date in the calendar table. If both conditions are true, it marks the project as ongoing for that month.

Step 4: Create Measures

Now, you can create measures to count the ongoing projects for each month. Create a measure like this:

OngoingProjectsCount = COUNTROWS(FILTER(Table1, Table1[IsOngoing] = 1))

Step 5: Visualize the Data

Use a visual (e.g., a line chart or a table) to display the ongoing project count for each month using the "OngoingProjectsCount" measure.

This approach should help you count ongoing projects for each month within the specified date range. Adjust the DAX formulas based on your actual column names and data model structure