DAX Formula for Counting New Cases Open in Fiscal Week

Objective

To create a DAX formula that counts the number of new cases that are open in a specified fiscal week.

Assumptions

The following assumptions are made for the formulation:

1. There is a table named Cases containing the columns:

   • CaseID: Unique identifier for each case.
   • OpenDate: Date when the case was opened.
   • FiscalWeek: The fiscal week associated with each case (this could be calculated based on the OpenDate).

2. A Date table is available with a FiscalWeek column for filtering.

DAX Formula

To count the number of new cases opened in the current fiscal week, use the following DAX formula:

NewCasesCount =
CALCULATE(
    COUNTROWS(Cases),
    Cases[OpenDate] >= FIRSTDATE(Date[FiscalWeekStart]) &&
    Cases[OpenDate] < FIRSTDATE(Date[FiscalWeekEnd])
)

Explanation of the Formula

1. CALCULATE Function: This is a powerful DAX function that changes the context in which data is evaluated. It allows us to apply filters to our data model.

2. COUNTROWS Function: This function counts the number of rows in the specified table, in this case, the Cases table.

3. Filtering Logic:

   • Cases[OpenDate] >= FIRSTDATE(Date[FiscalWeekStart]): This condition checks if the case's opening date is greater than or equal to the start date of the current fiscal week.
   • Cases[OpenDate] < FIRSTDATE(Date[FiscalWeekEnd]): This condition ensures that the case's opening date is before the end date of the current fiscal week.

Practical Illustration

Scenario:

Suppose we want to find out how many new cases were opened during the fiscal week of January 1, 2023, to January 7, 2023.

Implementation:

1. Create a date table with fiscal week definitions:

   • FiscalWeekStart: January 1, 2023.
   • FiscalWeekEnd: January 8, 2023.

2. Utilize the formula in a calculated measure or a report visualization to display the count of new cases for that fiscal week.

Example Calculation:

If the Cases table has the following entries:

The resulting count from the DAX formula for the fiscal week would yield 3 (cases 1, 2, and 3).

Conclusion

The provided DAX formula effectively counts the number of new cases opened in the specified fiscal week by applying necessary filters to the relevant date conditions. Understanding this functionality is crucial for effectively analyzing case data within fiscal periods. For further learning and mastery of DAX, consider exploring the courses available on the Enterprise DNA Platform.