Forum Discussion
Checking condition for a data range
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
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
1 Reply
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