DAX Function for Daily Cost Calculation

This guide provides a DAX function to calculate daily costs based on a cost per day value from a SharePoint list. The resulting cost value can then be used for further calculations as needed.

Objective

The purpose of this function is to:

1. Retrieve the cost per day from a SharePoint list.
2. Calculate the total cost for a specified period based on the calculated daily cost.
3. Ensure the calculated cost can be utilized for additional analytics or reporting needs.

Assumptions

1. You have a 'Costs' SharePoint list with at least the following columns:

   • CostPerDay: The daily cost value.
   • Date: The date corresponding to the daily cost.

2. You want to create a total cost measure based on a specified date range.

DAX Code

The following DAX function creates a measure to calculate the total cost for a day:

Total Daily Cost = 
VAR StartDate = MIN('Date'[Date])  // Get the start date from the date column
VAR EndDate = MAX('Date'[Date])    // Get the end date from the date column
VAR DailyCost =
    SUMX(
        FILTER(
            'Costs',
            'Costs'[Date] >= StartDate && 'Costs'[Date] <= EndDate
        ),
        'Costs'[CostPerDay]
    )
RETURN
    DailyCost * COUNTROWS(DISTINCT('Date'[Date]))  // Calculate total cost over the range

Breakdown of the Code

1. Variables

   • StartDate: The minimum date (earliest) in the selected date range.
   • EndDate: The maximum date (latest) in the selected date range.

2. Daily Cost Calculation

   • The SUMX function iterates over the 'Costs' table filtered by date to calculate the total daily cost between StartDate and EndDate.

3. Returning the Total Cost

   • The total cost is calculated by multiplying the DailyCost by the count of distinct days in the date range.

Input Validation

1. Ensure that the 'Costs' table is not empty.
2. Verify that the CostPerDay column contains numeric values.
3. Ensure that the Date column is correctly formatted as date.

If these checks are not satisfied, errors will be raised, and the function will return a blank value.

Usage Example

Assuming you've created a report in Power BI and want to use this measure to display the total costs in a visual, you can simply drag the Total Daily Cost measure into your report canvas.

Example Scenario

1. Data Structure
   Your 'Costs' table might look like this:

   Date	CostPerDay
   2023-10-01	100
   2023-10-02	120
   2023-10-03	110

2. Using the Measure
   You apply a date filter on your report that selects the dates from October 1 to October 3, which will result in the measure calculating a total cost of:

   • (100 + 120 + 110) * 3 = 330.

Conclusion

This DAX function efficiently calculates total costs based on daily values while allowing for flexibility in analytics. To enhance your DAX skills and understanding, consider exploring courses on the Enterprise DNA Platform.

This approach maintains the best practices by validating inputs, documenting the functions clearly, and providing a structured framework for future calculations.