Code Generator | DAX

DAX Measure for Summing Active Care Package Duration

This guide details creating a DAX measure in Power BI to sum the 'Duration' of active care packages from January to August 2024, incorporating relationships with a Calendar table and dynamic filtering by service type and provider.

Prompt

ok i have a data set in power bi and i need some measures to make some calculations. the table is called Data.
the columns are: Person Id, Person Name, DoB, DoD, Gender, Ethnicity, Primary SUG, Purchase Order, Provider, Provider Sector, Allocated Party, Package Type, Service Type, Service Group, Element Type, Element Start Date, Element End Date, Commitment Start Date, Commitment End Date, Notional, Separate BH Rate, Cost per, Frequency, Quantity, Duration.
the data is care visits to peoples homes. each line of data is 1 persons package and the duration is a weekly hours total.
people can have more than 1 package so they can appear on the list more than once. 
the report timeframe is 1/1/24 to 31/8/24 everything open between these dates, so there are Element Start Dates before 1/1/24 if there end date is null or after 1/1/24. 
what i need is a measure to sum the duration column for everything open each month from jan 24 to Aug 24.
i have a formula in excel that acheives this: =SUMIFS(Data!$Y:$Y,Data!$P:$P,"<="&Sheet1!C$3,Data!$Q:$Q,">="&Sheet1!C$3,Data!$M:$M,Sheet1!$B$4,Data!$I:$I,Sheet1!$B7)+SUMIFS(Data!$Y:$Y,Data!$P:$P,"<="&Sheet1!C$3,Data!$Q:$Q,"",Data!$M:$M,Sheet1!$B$4,Data!$I:$I,Sheet1!$B7)
in this Data relates to the data sheet, sheet1 relates to the sheet i'm using to create the tables. Column Y in Data is the Duration, Column P in Data is Element Start Date, cell C3 is 01/04/2024, column Q in data is Element End Date, Column M in Data is Service Type, cell B4 is "Care at Home - Option 3", column I in data is Provider and cell B7 is the first provider in a list in this case "Adigo Care".
this formula gives me the sum of the duration for everything meeting though parameters open within april 24. i then drag the formula along to get the same result for other months, and down for other providers.


i need this to be the most efficient way with no errors, please take into account all aspects of this request and consider if a new calendar table is required in PBI and if there needs to be relationships between that and both Element Start Date and Element End Date. is there anything else you need me to answer before you respond to this request? the provider should not be fixed as i wish to see all providers in a list similarly i need to see all months in a table

Answer

DAX Measure for Summing Duration Based on Conditions

Objective

To create a DAX measure that sums the Duration for care packages that are active in each month from January 2024 to August 2024, based on the provided criteria, including relationships with an optional Calendar table.

Assumptions

You have a table called Data with specified columns.
There is a Calendar table for the months of interest (January 2024 - August 2024).
The relationships between the Calendar table and Element Start Date as well as Element End Date are established.

Necessary Imports

In Power BI, no specific import statement is needed for DAX calculations as it is integrated into the Power BI environment.

Step-by-Step Implementation

1. Create a Calendar Table

You should create a Calendar table if you haven't done so. It helps in managing time-based calculations efficiently.

Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2024, 1, 1), DATE(2024, 8, 31)),
    "Month", FORMAT([Date], "MMMM YYYY"),
    "Year", YEAR([Date])
)

2. Establish Relationships

Make sure to create these relationships in the Model view:

Calendar[Date] to Data[Element Start Date]
Calendar[Date] to Data[Element End Date]

3. Create the Measure

Now, define a DAX measure to sum the Duration where the conditions are met.

Total Duration Active = 
VAR SelectedDate = MAX(Calendar[Date])  -- Get the selected date from Calendar
RETURN
SUMX(
    FILTER(
        Data,
        Data[Element Start Date] <= SelectedDate && 
        (Data[Element End Date] >= SelectedDate || ISBLANK(Data[Element End Date])) &&
        Data[Service Type] = SELECTEDVALUE(YourServiceTypeTable[Service Type]) &&  -- Assumes you're slicing by Service Type
        Data[Provider] = SELECTEDVALUE(YourProviderTable[Provider])  -- Assumes you're slicing by Provider
    ),
    Data[Duration]  -- Sum Duration
)

4. Explanation of the Code

Variable Declaration: VAR SelectedDate captures the maximum date from the calendar (which will change based on your slicer/selectors).
FILTER Function: Filters the Data table based on your specified conditions:
- The Element Start Date must be on or before the selected date.
- The Element End Date must either be on or after the selected date, or it must be blank (indicating an ongoing package).
- Enables filtering by Service Type and Provider dynamically from the report context.
SUMX Function: Iterates over the filtered table and sums up the Duration.

Example Usage

To visualize this measure in Power BI:

Create a line chart or table that includes the Month from the Calendar table.
Add Total Duration Active as the Values field.
Use slicers for Service Type and Provider for dynamic filtering.

Final Note

Ensure your model's relationships and data types are correctly set. The Calendar table is essential for time intelligence functions, and using slicers allows dynamic filtering on multiple dimensions without hardcoding values.

Should you wish to explore advanced DAX lessons or available resources, consider checking the Enterprise DNA Platform for courses tailored to enhance your DAX proficiency.