DAX Function to Calculate Daily Costs from SharePoint List

This document outlines a DAX function to calculate the cost per day based on the cost per day entries from a SharePoint list, encompassing the given date range.

Assumptions

• The cost data is stored in a SharePoint list with columns: StartDate, EndDate, and CostPerDay.
• The dates should be parsed as DateTime data types.
• The calculations will generate an output table with daily costs for the specified date range.

Function Overview

The function will:

1. Accept a date range.
2. Calculate costs for each day within that range based on CostPerDay.
3. Return a formatted table showing the date and respective daily costs.

DAX Code

DailyCosts = 
VAR DateRangeStart = DATE(2023, 1, 1)  // Adjust the start date as needed
VAR DateRangeEnd = DATE(2023, 1, 31)   // Adjust the end date as needed

RETURN 
ADDCOLUMNS(
    CALENDAR(DateRangeStart, DateRangeEnd), // Create a calendar table for the date range
    "Daily Cost", 
    VAR CurrentDate = [Date]
    VAR CostRecord = 
        FILTER(
            SharePointList,   // Replace with your actual SharePoint list name
            CurrentDate >= [StartDate] && CurrentDate <= [EndDate]
        )
    RETURN 
    IF(
        COUNTROWS(CostRecord) > 0, 
        SUMX(CostRecord, [CostPerDay]),  // Summing the CostPerDay for valid date ranges
        BLANK()  // Return BLANK if no cost record found for the date
    )
)

Explanation of Key Components

1. Date Variables:

   • DateRangeStart and DateRangeEnd specify the range for which we are calculating the costs.

2. CALENDAR Function:

   • Generates a continuous list of dates from DateRangeStart to DateRangeEnd.

3. ADDCOLUMNS Function:

   • Adds a new calculated column "Daily Cost" to the generated calendar.

4. FILTER Function:

   • Filters the SharePoint list to find cost records applicable to the current date.

5. IF Statement:

   • Checks if any record exists for the date before attempting to sum costs, returning BLANK if none are found.

Input Validation

• Ensure that StartDate and EndDate from the SharePoint list are valid date types.
• Handle edge cases where no cost records exist for the date range.

Example Usage

To utilize the DailyCosts calculation in a Power BI report or dashboard, you can create a visual using the output table displaying Date and Daily Cost.

Example Scenario

• A SharePoint list includes costs like:
  • StartDate: 2023-01-01, EndDate: 2023-01-05, CostPerDay: 100
  • StartDate: 2023-01-03, EndDate: 2023-01-10, CostPerDay: 150

When you compute costs from 2023-01-01 to 2023-01-31, the results will yield:

• From 2023-01-01 to 2023-01-02: $100 per day.
• From 2023-01-03 to 2023-01-05: $100 per day + $150 per day for 2023-01-03 to 2023-01-10.

This example will successfully yield the daily costs dynamically across your visualizations based on your datasets.

Conclusion

This DAX function efficiently calculates daily costs from a SharePoint list and can be extended or modified to cater to more complex requirements. For advanced learning on DAX functions, consider exploring courses on the Enterprise DNA Platform.