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.
Cummulative Count
Hi Community I need your help! I have the following scenario: I have a list of tasks that are related to a sprint. In this tabele ALL tasks (active and closed) are listed. For each Task ID I have the following collumns. The collumn "Active Date" presents the day on which a task was in the state "Active". The collumn "Closed Date" represents the day on which a task was in the state "Closed/Compleded". Now, what I'd like to do is to show in a visual the cummulative count of active and closed/completed tasks during the days of one selected Sprint. For the sprint of calendar week 43 the visual should look similar to picture 1. I now that I need two different measures (one for the cummulative count of the tasks that are "active", and one for those that are "compleded") however the difficulty is that it should only cummulatively count the respective tasks based on the selected Sprint Week in the Filter. This means that with each start of a new sprint the measure begins to count from 0 and does not include the previous sprints into the calculation. I've tried to make it more clear with table 2. I hope that you guys can help me! Thank you in advance! Here is some sample data: Task ID Sprint CW Sprint Start Date Sprint End Date Activated Date Completed Date 1 41-42 08.10.2022 22.10.2022 05.10.2022 20.10.2022 2 41-42 08.10.2022 22.10.2022 06.10.2022 10.10.2022 3 41-42 08.10.2022 22.10.2022 06.10.2022 11.10.2022 4 41-42 08.10.2022 22.10.2022 06.10.2022 20.10.2022 5 41-42 08.10.2022 22.10.2022 06.10.2022 25.10.2022 6 41-42 08.10.2022 22.10.2022 07.10.2022 10.10.2022 7 41-42 08.10.2022 22.10.2022 07.10.2022 20.10.2022 8 41-42 08.10.2022 22.10.2022 12.08.2022 19.10.2022 9 41-42 08.10.2022 22.10.2022 12.08.2022 19.10.2022 10 41-42 08.10.2022 22.10.2022 12.10.2022 12.10.2022 11 41-42 08.10.2022 22.10.2022 13.10.2022 17.10.2022 12 41-42 08.10.2022 22.10.2022 14.08.2022 null 13 41-42 08.10.2022 22.10.2022 14.10.2022 21.10.2022 14 41-42 08.10.2022 22.10.2022 16.09.2022 20.10.2022 15 41-42 08.10.2022 22.10.2022 18.10.2022 18.10.2022 16 41-42 08.10.2022 22.10.2022 19.08.2022 10.10.2022 17 41-42 08.10.2022 22.10.2022 19.10.2022 20.10.2022 18 41-42 08.10.2022 22.10.2022 20.09.2022 20.10.2022 19 41-42 08.10.2022 22.10.2022 20.10.2022 21.10.2022 20 41-42 08.10.2022 22.10.2022 20.10.2022 21.10.2022 21 41-42 08.10.2022 22.10.2022 20.10.2022 21.10.2022 22 41-42 08.10.2022 22.10.2022 20.10.2022 25.10.2022 23 41-42 08.10.2022 22.10.2022 20.10.2022 21.10.2022 24 41-42 08.10.2022 22.10.2022 21.10.2022 21.10.2022 25 41-42 08.10.2022 22.10.2022 23.09.2022 20.10.2022 26 41-42 08.10.2022 22.10.2022 27.05.2022 20.10.2022 27 41-42 08.10.2022 22.10.2022 28.06.2022 20.10.2022 28 43-44 22.10.2022 05.11.2022 01.07.2022 null 29 43-44 22.10.2022 05.11.2022 03.10.2022 25.10.2022 30 43-44 22.10.2022 05.11.2022 04.10.2022 null 31 43-44 22.10.2022 05.11.2022 06.10.2022 25.10.2022 32 43-44 22.10.2022 05.11.2022 07.10.2022 null 33 43-44 22.10.2022 05.11.2022 11.08.2022 null 34 43-44 22.10.2022 05.11.2022 11.10.2022 null 35 43-44 22.10.2022 05.11.2022 11.10.2022 null 36 43-44 22.10.2022 05.11.2022 11.10.2022 null 37 43-44 22.10.2022 05.11.2022 11.10.2022 null 38 43-44 22.10.2022 05.11.2022 12.10.2022 null 39 43-44 22.10.2022 05.11.2022 12.10.2022 null 40 43-44 22.10.2022 05.11.2022 12.10.2022 null 41 43-44 22.10.2022 05.11.2022 12.10.2022 null 42 43-44 22.10.2022 05.11.2022 12.10.2022 null 43 43-44 22.10.2022 05.11.2022 12.10.2022 null 44 43-44 22.10.2022 05.11.2022 12.10.2022 null 45 43-44 22.10.2022 05.11.2022 12.10.2022 null 46 43-44 22.10.2022 05.11.2022 12.10.2022 null 47 43-44 22.10.2022 05.11.2022 12.10.2022 null 48 43-44 22.10.2022 05.11.2022 12.10.2022 null 49 43-44 22.10.2022 05.11.2022 12.10.2022 null 50 43-44 22.10.2022 05.11.2022 12.10.2022 null 51 43-44 22.10.2022 05.11.2022 12.10.2022 null 52 43-44 22.10.2022 05.11.2022 12.10.2022 null 53 43-44 22.10.2022 05.11.2022 13.10.2022 27.10.2022 54 43-44 22.10.2022 05.11.2022 14.10.2022 null
Calculate future cumulative stock with missing dates
Hi, I want to calculate the cumulative stock per week based on the current stock and the planned consumption in the upcoming weeks and put it in a matrix. The measure i'm using is: cumulative stock = calculate([Current stock] - 'Planned'[Planned consumption],FILTER('Date','Date'[Date] >= today())) Where "planned consumption" is a measure of the sum of the planned quantity in table "Planned". In some of the weeks there are no orders planned so in my planned consumption table there are dates/weeks missing. I'm using a date table which is linked to the planned consumption tabel based on the planned date. Example of values in table "Planned" The current outcome of my measure is: The numbers in green are my desired results. What do I need to adjust to make this work? Thanks in advance! 🙂
Easy in Excel but not in Dax Measure (running total)
Hi all, I need to replicate a calculation I have in Excel using a DAX measure. It's to get a running total. It's B3 - C3 + D2 (where D2 is the running total copied down the column). Can column D be calculated using a dax measure in PowerBI? I can calculate the running total of column B using: running total measure of amount1 := VAR IndexID = SELECTEDVALUE( 'ID_Table'[ID], 0 ) RETURN SUMX ( FILTER ( SUMMARIZE ( ALLSELECTED ( 'ID_Table' ), 'ID_Table'[ID], "RunningTotal", [Amount1] ), 'ID_Table'[ID] <= IndexID), [RunningTotal] )Performance of cummulative measure
Hi! I struggle with below measure performance. Could you please help me rewrite it, so it doesnt need that much memory? Its finishes up with "Couldn't load the data for this visual. There is not enough memory to complete this operation. (...)" Its working on 4.000.000 rows table 'Sales', pbix is 45mb and PowerBI Desktop is a 64bit version. Sales_DayCummulative = var LastSalesTime = CALCULATE( MAX( 'Sales'[Time]); All( 'Sales')) return IF( SELECTEDVALUE( 'Sales'[Time]) > LastSalesTime; BLANK(); CALCULATE( SUM( 'Sales'[Value]); FILTER( ALLSELECTED( 'Sales'); 'Sales'[Time] <= MAX ( 'Sales'[Time]) )))
