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.
Weekly closingBlance by AccountNo with missing WeekEndDate
Hi everyone, I need help in generating a matrix/bar chart with weekly closing balance. I have two datasets : Position Date Date has been created based on "AccountingDate" of Position table. I added a column "WeekEndDate" which is the end date of each week. I tried to get the last BalanceValue for each "AccountNo" at the end of the week. The problem is that sometimes, AccountNo has no record in a week. Thus value in matrix is wrong. Here is an example : If my WeekEndDate is 14/01/22, final week balance should be : 95808,99 + (-1646984,4056) + 91288,25 = -1459887,17 But in my matrix Balance of AccountNo 512411 was not took in the measure. Hence It returns : (-1646984,4056) + 91288,25 = -1555696,16 I currently use this method I found from another forum and tuned it with my dataset : lastnonblankdate = LASTNONBLANK ( 'Positions et mouvements bancaires réels'[Accounting Date], CALCULATE ( SUM ( 'Positions et mouvements bancaires réels'[BalanceDaily] ) ) ) lastnonblankvaue by category: = VAR _lastnonblankdatebycategory = ADDCOLUMNS ( SUMMARIZE ( 'Positions et mouvements bancaires réels', 'Mapping comptes'[Accounting No] ), "@lastnonblankdate", [lastnonblankdate] ) RETURN IF ( HASONEVALUE ('Date'[Week End Date]), CALCULATE ( SUM ( 'Positions et mouvements bancaires réels'[BalanceDaily] ), TREATAS ( _lastnonblankdatebycategory, 'Mapping comptes'[Accounting No], 'Date'[Date] ) ) ) Do somoes ever faced this issue and came up with an alternative or solution. Thank you!
Need Help - Searching and Comparing a String
I am having a little bit of an issue. I need to compare a string in one column to another, then provide the string in another column. The problem is I have to search the entire column and will not be a 1:1 match. Column A Column B Column C Column D ABC XYZ ABC XYZ DDC TTV DDC TTV In the example above, I want to search Column A where it matches Column C, and then provide output of Column B in Column D I appreciate anyone's help. Thanks, J
Calculation Involving Duplicates
Without deleting duplicates, how do I write a formula to calculate the total scrap percentage by date? Scrap% = (Machine Scrap/(Machine Scrap + Good Quantity)) * 100 Production Day Machine Scrap Good Quantity 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589
Getting 0 for grand total on a created Measure
Hello Everyone, I feel like this is an easy fix but im abselutely stumped..... I have seen a couple of forums for items similar to this but they were slightly different/specific and i wasnt sure how to work those methods into my situation. I have created a couple of measures. 1. is to caclualte Points times Qty of items. Spec Point QTY = MIN('SageProductMaster'[Points]) * SUM('spec_credits'[qty_0]) 2. is to use the previous measure(Spec Point QTY) to calculate total points times Dollars Spec Points = [Spec Point QTY]*SUM(spec_credits[untaxed]) While these formula gave me proper results in the view i dont get a grand total for the view it just says 0 This is where i cant seem to figure out how to get Grand Totals. Please Help =]Create a Parameter off of a Column Query
I'm trying to create a parameter using a Query from the values in my dataset. From all of the tutorials I've watched, creating a Parameter requires you to have static values (i.e. a series of %'s, dates, metrics, etc.), however I'd like to have my parameter values be based off of the values in a column of my data (these names will change every time I refresh the data). Is this possible to do? For Example, I have a dataset with a column named "Baseline Scenario" with a series of scenario names listed below. However, when I upload a new dataset, or refresh the data, these Scenario Names may change and will no longer match what is listed, there may be more, less, etc. What I'd like to do is have the Parameter Table read from whatever is listed here so that every time I refresh the data, I can have a slicer automatically know the scenario names based off of what was included in the data. Is this possible?Dinamically switch between two date fields and measures using slicer
Hi everyone, I'm trying to create a matrix, which can show different calculations depending on slicer selection: For example if Sellout is selected in slicer 1, and Base Volume (Kg) is selected in slicer 2, then the matrix will show SUM(Fact_Sales[Base Volume (Kg)], and so on. This is working as intended at the moment, however, the Date Field in the matrix columns also needs to change depending on what is chosen on slicer 1: SellIn -> Date field should be F_Sales[Sellin_Date] SellOut -> Date field should be F_Sales[Sellout_Date] Any ideas?Non intuitive total
Hi, Im having a logic doubt problem that I hope you can help with. I have a column with Orders, Distinct Customer, Orders per Distict Customer. orders = SUMX(Orders,Orders[Count]) distict Customer = DISTINCTCOUNT(Orders[Customer_code]) orders per distinct cust = [orders]/[distict Customer] All the numbers in that table are correct. However, the 2022 number is not intuitive. It should be around 56. How would you enforce that?
C# formula to DAX
I need help converting this C# formula to DAX. when I get to the italics part of this formula (below) my IF statement is breaking in DAX. "if (wrkgrp.Contains(""unsubmitted"")) { Logfiletype = ""UnSubmitted""; } else if (DateTime.ParseExact( ""InitialAdjDate"" TxnSubmitDataTime.ToString(""MM/dd/yyyy"") == item.UpdatedDataTime.ToString(""MM/dd/yyyy"") && !inststs. InstPlansts.ToLower().Contains(""stop"")) { Logfiletype = ""New Record""; } else if (inststs.ToLower().Contains(""stop"")) { Logfiletype = ""STOPPED""; } else if ( ""lastMonitoringCheckDate"" == null) { Logfiletype = ""New Record""; } else if (DateTime.ParseExact( ""lastMonitoringCheckDate"").ToString(""MM/dd/yyyy"") == item.UpdatedDataTime.ToString(""MM/dd/yyyy"")) { if (String.IsNullOrEmpty(item?.MonitoringElgChks.TOBROccured) && lastmemo.Contains(""moved from original BAN"")) { Logfiletype = ""New Record - TOBR""; } else { if (DateTime.ParseExact( ""lastMonitoringCheckDate"".ToString(""MM/dd/yyyy"") == item.CreatedDateTime.ToString(""MM/dd/yyyy"")) { Logfiletype = ""Monitoring - TOBR""; } else { Logfiletype = ""Monitoring""; } } } else { Logfiletype = ""Follow Up Credit""; }"
