Variable rate from Month to Month

[jvs5899]

Hello,

I have a project that I am unsure of how to program in DAX. We are developing a performance dashboard for a quality department. To do this, I am conducitng a time study to generate an approximate value for the length of time spent per quality event. Then I intend to simply multiply that value by the average bill rate. Very simple. However, a request has come from above to enable the caluclation to vary by month. i.e. they want to be able to make process improvements and update the amonth of hours spent per month, without wiping out the old values. They also want to be able to update the bill rate without wiping out the old values. 

At present I can only think of a fairly complex logic statement. Any suggestions how to program it easily in DAX is welcomed. 

[Anonymous]

Hi @jvs5899 

Have you solved your problem yet? Do the methods offered so far help you? If so, could you please accept it as a solution? This will help more users who are facing the same or similar difficulties. Thank you!

If your problem is not solved yet, please provide some sample data and expected results based on the sample data so that we can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

[rohit1991]

Hi @jvs5899 

Here is an example, I believe might be suited to your question.
1) Create a MonthlyRates Table: columns areYearMonth, HoursPerEvent, BillRate.

2) Create a DAX Measure: Total Cost = SUMX('QualityEvents', VAR CurrentMonth = FORMAT('QualityEvents'[EventDate], "YYYY-MM") RETURN LOOKUPVALUE('MonthlyRates'[HoursPerEvent], 'MonthlyRates'[YearMonth], CurrentMonth) *LOOKUPVALUE('MonthlyRates'[BillRate], 'MonthlyRates'[YearMonth], CurrentMonth))

3) Update Monthly Rates: Modify the MonthlyRates table to reflect process improvements or bill rate changes without overwriting historical values. This ensures historical accuracy and flexibility for future upadates.

[DataNinja777 · Accepted solution]

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,