Function to collect leftover hours if exceeding 8 hours of work?
I'm trying to configure a formula to calculate the 'Overage' Hours of a days work. From my data I did a 'GroupBy' within PowerQuery to condense all my columns to only the necessary fields to narrow down my calculation for easier functionality. However, I'm stuck on this final piece on this calculation. The goal is to be able to track how much time is worked past 8 hours (if you work 9 hours in 1 day, your overage hours would be 1 hour). In addition, a regular work week being 40 hours, I can exceed 40 hours and go into overtime within the same work day. I don't know how to manage this formula to sum 'Regular' hours and 'Overtime' hours together and subtract it from 8 to get the leftover hours worked. My current formula is super simple as I keep trying to build on it, but don't know how to go about it. =IF(SUM[Hours]-8<=0,"",SUM[Hours]-8) Date Category Person Hours 01/20/2025 Regular A 5.5 01/20/2025 Overtime A 3.5 01/20/2025 Regular B 5 01/20/2025 Regular B 4 Person A 1/20/2025 = 1 Overage hour (but 3.5 hours of Overtime) Person B 1/20/2025 = 1 Overage hour (No overtime accumulated yet) This is over thousands of rows of variances of hours worked. Apologies if it doesn't make much sense I can try to clarify more if asked. But I'm still fairly a novice at DAX and Power Query but would love some insight and advice on how to get this solved.
Cummulative turnover rate based on turnover in month
Hi fellow BI-enthusiasts, I need some help with the calculation of the cummulative turnover rate for different BU's in my organisation. I already have a measure that calculates the turnoverrate (number of terminated contracts/number of active employees). This measure also works when I want the turnoverrate per Business Unit per month. The standard within my organisation for reporting turnover is the cummulative percentage of given month and the 11 months prior. However this is not as simple as it seems. Let me demonstrate with an example: Business Unit X in February 2023: - 66 active employees - 2 employees left - Turonoverrate of 3.03% (2/66) In December 2023: - 56 active employees - 1 employee left - Turnoverrate of 1.79% (1/56) This is the data in the given months. However, in a selected month, I want the status of the current month and the 11 months before it cumulatively. This must be the sum of the different turonverrate's. E.g. Business Unit X's cummulative percentage in December 2023 should be: In this case 3.03% (Feb 2023) + 1.79% (Dec 2023) = 4.82% = CORRECT So what I don't want is for everything to be added together like: A total of 76 people were employed in those two months and 3 left employment, which gives you a ratio of 3.95% (3/76) = INCORRECT. I hope this example clarifies my situation. P.S. I might add that my data comes mostly form a SQL-server and is imported. I tried several formules from Chat GPT, but they were either wrong or gave me the following error: "Visual has exceeded available resources", making it specifically hard to check whether chat GPT's formulas are correct.
Inflation index calculation - easy in excel but difficult in DAX
Year Inflation Inflation Index 2024 0.03 1 2023 0.02 0.970874 2022 0.01 0.951837 2021 0.01 0.942413 2020 0.02 0.933082 2019 0.02 0.914786 simple table, this year index is always 1 year 2023 is calculated as (2024 index / (1 + 2024 inflation) year 2023 is calculated as (2023 index / (1 + 2023 iflation) very easy in excel but cannot do it in PBI as the measure references to itself can you please help ❤️
Get total miles driven in a table
Hi Everyone, I have a table in power bi which is rows for each driving trip users enter. They only enter the ending mileage of the car. What I need to do is show in the table how many total miles driven based on taking the ending trip mileage and subtracting it from the last ending trip entered for that car. Here is the table so far for 1 vehicle. As you can see you can have multiple trips entered on the same day. So if I look at the second one in the list the miles driven should be 22 because 114319 the ending mileage - the ending mileage of the trip before it is 22 miles driven. How would I do this?
Using existing data in one column to calculate a delta value in another column with DAX
Hi there So I have a dataset with 2 columns: "Timestamp" and "NumericValue" My objective is to create a new column which uses DAX to calculate the change in NumericValue. It should obviously start off with the earliest timestamp date and calculate based off previous value in NumericValue. As new numbers are added continously the logic should be generic and work automatically. To give context i want to calculate the change of water consumption coming from an IoT water sensor only providing the meter (accumulated total consumption) My table "Measurements" with examples: | Timestamp | NumericValue | Delta_Value | |--------------- |------------------|---------------| | 2024-01-01 | 0 | 0 | | 2024-01-02 | 10 | 10 | | 2024-01-03 | 15 | 5 | | 2024-01-04 | 16 | 1 | etc etc I have tried a lot including chatgpt suggestions, but it doesn't really work. I made with work in Javascript, but i really want it to work using DAX Thank you!
XIRR Calculation with Filters
I am trying to calculate an XIRR function in Power BI but I am having difficulties. Specifically, we are looking to create a dynamic IRR function in Power BI that would allow us to calculate the IRR for each investment based on a certain period end – for example, would like to calculate a since inception to-date XIRR for “investment A” but also have the ability to back into prior period XIRRs as well. As you can see, we have multiple underlying investments, each which has their own specific IRR calculation and that is where we are getting tripped up. Basically, the end goal is to be able to choose a date from a drop down, and have a table showing each investment and their respective IRR based on the chosen date. One thing to note is that the data includes a “Commitment” Type, which needs to be excluded from the calculation as well. Any tips or help is greatly appreciated. Here is some Sample Data. Let me know if there is anything else I can provide to help the process. Security Type Value Trade Date Investment A Commitment 100 1/1/2022 Investment A Contribution -50 8/31/2022 Investment A Valuation 50 9/30/2022 Investment A Income 5 11/30/2022 Investment A Distribution 25 12/15/2022 Investment A Valuation 23 12/31/2022 Investment A Contribution -50 2/1/2023 Investment A Distribution 5 3/15/2023 Investment A Valuation 70 3/31/2023 Investment A Distribution 15 4/15/2023 Investment B Commitment 500 1/1/2022 Investment B Contribution -200 8/15/2022 Investment B Contribution -300 1/15/2023 Investment B Distribution 100 9/15/2022 Investment B Income 50 10/31/2022 Investment B Valuation 103 9/30/2022 Investment B Valuation 55 12/31/2022 Investment B Valuation 275 3/31/2023 Investment B Distribution 100 2/15/2023 Investment C Commitment 50 1/1/2022 Investment C Valuation 15.5 12/31/2022 Investment C Valuation 16 3/31/2023 Investment C Valuation 10 6/30/2023 Investment C Contribution -30 10/15/2022 Investment C Contribution -20 4/15/2023 Investment C Distribution 15 11/15/2022 Investment C Distribution 25 5/15/2023Wrong total amount but correct for each row
Hello, We are experiencing difficulties with calculating the total Cost + a percentage. The issue arises from multiplying the Cost by a specific percentage (margin). While our calculation works correctly for individual campaigns, errors occur when calculating the total amount because it also gets multiplied by the highest margin in the table. We retrieve the Cost from the Premium_facts table and the Margin from the OPI_dimensions table. Our current calculation is as follows: Total Cost = Sum(Premium_Facts[Cost]) / (1 - Max(OPI_dimension[Margin])). Is there a way to exclude the subsequent multiplication of the total value by the margin using a solution in Funnel.io or PowerBI? The attached image demonstrates that the calculation for each campaign is accurate, but when the values are aggregated, the total amount becomes significantly higher than expected: We hope that you can assist us in resolving this issue. Please don't hesitate to reach out if you require additional information or have any questions. Thank you very much in advance. Best regards Sofie & Christian
