Variable rate from Month to Month

Hi jvs5899 ,

To handle your requirement of calculating the cost of quality events in Power BI, while allowing for dynamic updates to both the time spent per event and the bill rate by month, you can achieve this using two disconnected tables. These tables will store historical values for time spent per event and bill rates on a monthly basis. The goal is to ensure that process improvements or bill rate changes don't overwrite historical data but instead adjust future values as required.

First, create a table called TimeSpentPerEvent that contains the month, event type, and the time spent (in hours) for each event. This table should look something like this:

TimeSpentPerEvent = 
DATATABLE(
    {"Month", "Event Type", "Time Spent (Hours)"},
    STRING, STRING, DECIMAL,
    {
        {"Jan 2024", "Inspection", 2.5},
        {"Feb 2024", "Inspection", 2.3},
        {"Mar 2024", "Inspection", 2.1},
        {"Jan 2024", "Review", 1.2},
        {"Feb 2024", "Review", 1.0}
    }
)

Similarly, create another table called BillRate to capture the bill rates for each month:

BillRate = 
DATATABLE(
    {"Month", "Bill Rate (per Hour)"},
    STRING, DECIMAL,
    {
        {"Jan 2024", 120},
        {"Feb 2024", 125},
        {"Mar 2024", 130}
    }
)

Next, make sure your main fact table (where the quality events are recorded) is linked to a Calendar table. The TimeSpentPerEvent and BillRate tables will remain disconnected from the main fact table to allow for flexible lookup based on the selected month.

Once the tables are set up, create a DAX measure to dynamically retrieve the time spent per event based on the selected month and event type. The following measure achieves this by using the LOOKUPVALUE function:

TimeSpentPerEvent = 
VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month])
VAR EventType = SELECTEDVALUE(Events[Event Type])
RETURN
    LOOKUPVALUE(TimeSpentPerEvent[Time Spent (Hours)], 
                TimeSpentPerEvent[Month], SelectedMonth, 
                TimeSpentPerEvent[Event Type], EventType)

Finally, to calculate the total cost for each quality event, multiply the time spent by the bill rate. This measure can be defined as follows:

TotalCost = 
[TimeSpentPerEvent] * [BillRate]

With this setup, you can update the TimeSpentPerEvent and BillRate tables as process improvements are made, without impacting historical values. The measures automatically adjust to reflect the correct values for each month. This approach provides a scalable and dynamic solution, ensuring that changes in time spent or bill rates are handled efficiently without requiring complex logic statements.

Best regards,