How Can I Calculate the Years Service of each employee
I have Table EmploymentDetails I will mention the importent columns EmploymentStartDate , PersonnalNumber I want to calculate the Years of Service and the number of months in Years of Service as You shown above I tired to create column to calculate Years of Service " Years of Service = VAR Years = DATEDIFF(EmploymentDetails[EmploymentStartDate],TODAY(), YEAR) VAR Months = DATEDIFF(EmploymentDetails[EmploymentStartDate],TODAY() , MONTH) - (Years * 12) VAR result = Years + (Months / 12) RETURN ROUND(result, 1) " this Measure worked correctly it has calculated the Years of Service from EmploymentStartDate of each employee untily today but I want to calculate the Years of Service depend on the slicer the employee's years of service are calculated based on the date I tried to create this Column " YearsOfServices = VAR Years = DATEDIFF(EmploymentDetails[EmploymentStartDate], SELECTEDVALUE(EmploymentDetails[EmploymentStartDate]), YEAR) VAR Months = DATEDIFF(EmploymentDetails[EmploymentStartDate], SELECTEDVALUE(EmploymentDetails[EmploymentStartDate]), MONTH) - (Years * 12) VAR result = Years + (Months / 12) RETURN ROUND(result, 1) " but there is not result could anyone help me to create a column and Measure please ? thank in advance
"Sorting Matrix Data Based on Slicer Selection
Hi Everyone, I want to sort the data in a matrix in a specific order. When I select a value in the slicer, Below is the raw data image : The matrix contains Product and Value [Measure]. Raw Data Slicer : The order in which the products need to be sorted on the year value selected in the slicer. Raw Data: Table Product Year Value P3 2023 100 P2 2023 200 P1 2023 300 P8 2023 40 P6 2023 100 P1 2024 200 P3 2024 300 P2 2024 40 P14 2024 100 P12 2024 200 slicer slicer Year 2023 2024 Order needed Order Required [ when I select year in Slicer ] 2023 2024 Order order P1 P1 P2 P2 P3 P3 P6 P12 P8 P14 BeaBF
Dynamic Measure Required with two slicer
Hi Folks, I'm trying to develop the dashboard by comparing two months and their uses of two slicers. I prepared the dashboard using Excel Formulas. please help to develop Power-BI As the Raw data is attached Excel is available. please refer it Google Drive Link for Excel File Please feel free to contact for more info if required. RAW Data Period Account Value X Value Y Value Z Fix Tata 4 3 5 Fix Birla 6 7 8 Fix Adani 7 8 9 Fix RIL 8 9 10 Fix Tata 9 10 11 Fix Birla 10 11 12 Jan Adani 11 12 13 Jan RIL 12 13 14 Jan Tata 13 14 15 Jan Birla 14 15 16 Jan Adani 15 16 17 Feb RIL 16 17 18 Feb Tata 17 18 19 Feb Birla 18 19 20 Feb Adani 19 20 21 Feb RIL 20 21 22 March Tata 21 22 23 March Birla 22 23 24 March Adani 23 24 25 March RIL 24 25 26 March Tata 25 26 27 March Birla 26 27 28 March Adani 27 28 29 April RIL 28 29 30 April Tata 29 30 31 April Birla 30 31 32 April Adani 31 32 33 April RIL 32 33 34 April Tata 33 34 35 Regards, MOHIT
Dynamic header for Date
Hey Community, I am trying to create a dynamic column header for my dashboard. My current table looks like this when the slicer filter is for 'Nov 2019'. What I want is something like this: Changes that I want to include: Sum of Generation column reflects the Month & Year M-1_Gen is the generation value of the previous month - So it should be named as prev month & year, i.e., Oct-2019 M-12_Gen is generation same month last year. So it should be Nov 2018. And likewise. I've included my sample files. Any help will be very appreciated. Thanks in advance. Files Here
Add New Dynamically Updating Column To Data
Hi, I'm relatively new to using DAX in PowerBI and have turned to ChatGPT for some help but it's repeatedly giving me 2 solutions, neither of which work! The Data: I have just one data table of inbound calls into a call centre. Columns include: StartDateTime, Agent Name, Caller Number (among others). Inbound calls can be answered by different agents, so a caller number may appear 20 times on 20 different datetime occasssions and the caller may have spoken to 5 different agents. StartDateTime AgentName CallerNumber 02/09/2023 19:01 Bob Caller01 02/09/2023 19:15 Alice Caller01 07/09/2023 15:33 Alice Caller02 13/09/2023 12:04 Bob Caller01 I have created some calculated fields including: MinCallStartDateTime (returns the lower value of the date filter applied to the dashboard). MaxCallStartDateTime (returns the upper value of the date filter applied to the dashboard). MaxCallsMade (returns count of caller number based on date filters applied to dashboard). FirstCall (returns the datetime of the first call from the selected Caller Number based on the date filters applied to the dashboard). For Caller01 MinCallStartDateTime 01/09/2023 MaxCallStartDateTime 15/09/2023 MaxCallsMade 3 FirstCall 02/09/2023 19:01 What I want to achieve: 1) I want to know which agent answered the FirstCall "FirstCallAgent" - my dashboard currently returns the FirstCall PER agent (for each Caller Number Selected), I just care about who was the first agent to answer the very first inbound call (dependent on date filters applied to the dashboard). 02/09/2023 19:01 Bob 02/09/2023 19:15 Alice (this currently shows but I don't care about it) If possible it would be ideal to create a new column called "CallTally" to the table which can be updated to count each inbound call from that number dynamically to update based upon any date filters applied to the dashboard. StartDateTime AgentName CallerNumber CallTally - may change if date filter changes 02/09/2023 19:01 Bob Caller01 1 02/09/2023 19:15 Alice Caller01 2 07/09/2023 15:33 Alice Caller02 1 13/09/2023 12:04 Bob Caller01 3 2) "RepeatCallPerc" - The goal is to find out which agent is best at resolving customer problems meaning there will be fewer repeat calls / MaxCallsMade from Caller Number where one agent answered the very first call, relative to if another agent answered that first call (since a call can be answered by any agent). Bob 100% (100% of calls, where Bob answered the first call, called in at another time)(Caller01) Alice 0% (0% of calls, where Alice answered the first call, called in at another time)(Caller02) The best way I can think about doing this is to calculate: for each agent - SUM the total instances of CallTally= 1 (according to the new column created as part of step 1). Then divide this by the SUM of the total MaxCallsMade for caller numbers whereby the agent answered the FirstCall (regardless of which agents answered future calls) - does that make sense? Extra: 3) A third goal would be to isolate which agent answered the last call "LastCallAgent" from each caller number (meaning that agent was the one to resolve that query) - to be able to sum this up and see that calls handled by Agent X are more likely to be resolved (and therefore the customer is less likely to call in) compared to calls handled by Agent Y. Expressing this as a percentage for comparison reasons would also be useful "LastCallAgentPerc" = Count LastCallAgent / TotalLastCalls = SUM("LastCallAgent"). But again this would require updating the above table /column tallying inbound calls within the filtered date range. Bob 50% (50% calls Bob handled were "last calls" (Caller01) Alice 50% (50% of calls Alice handled were "last calls" (Caller02) Anyway, I have absolutely no idea how to begin this! Or if it's even possible. I would appreciate any insight and expertise you may be able to add to this. Or if you can add an easier way to think about this. Thanks.
5 years rolling data based on slicer (year) selection
Hi All, I have a requirement that needs to display 5 years of data if selected YTD, 12 months of data if selected MTD, selected month && 4 quarters of data if selected QTD. How do I get 5 years of data? Please drop me your suggestions or answers.
Is it possible to use a visualization/slicer, that edits a DAX formula in the Data tab?
Hello, I want to use a slicer in the Reports tab that would allow a user to change a varible that would subsequently change a column formula created in the Data tab. For Example, the below image shows a date slider (let's call this date variable "SlicerDate"). This would be a visualization in the reports tab that any user can interact with. I want to create an IF statement in the Data tab that states, IF(X<SlicerDate, "Earlier", IF(X>SlicerDate, "Later")). Although the actual column formula in the Data tab would not change, the "SlicerDate" within the formula would change according to the date applied to the slicer. The reason for this is there are many people who will view this report themselves and would like to adust the returned variables based on a specific date, but not all people will know how to change data portion of the PBIX file or online file. I want users to be able to change the variable returned in the Data tab without leaving the Reports tab. Any assistance or suggestion is greatly appreciated. Thank you!Help creating a classifier from columns using RANKX
Hello, I am currently trying to create a custom column in Power BI to classify email campaigns based on certain criteria. The column is called "CRITERIO" and I am using the SWITCH function to assign values to each campaign based on its name and the number of emails sent. Here is the formula I am using: The formula is supposed to assign values of "AURIS" or "TEST" to campaigns whose names contain those words, and "TOP 5", "TOP 6-10", or "TOP 11-15" to campaigns whose email volumes rank within those thresholds. All other campaigns should be assigned a value of "OTHER". Here's and data's example: RECIPIENT_ID EVENT_TYPE NOMBRE EMAIL 607227625501 Sent LPR [email protected] 579440932400 Open [AURIS] SIN CONTACTO API [email protected] 579440932952 Sent [TEST] LISTO PARA RETIRO API [email protected] 579440957813 Sent ENCUESTA [email protected] 618127727620 Sent POLL [email protected] 565900248891 Sent WELCOME [email protected] Data Base Sample However, I am running into a few issues. First, the formula is not correctly filtering the "TOP 5" campaigns, and is instead assigning that value to all campaigns that are not "AURIS" or "TEST". Second, occasionally the formula is creating a circular dependency that prevents me from using the "COUNT" function instead "DISTINCTCOUNT". I have tried a few different variations of the formula, including using COUNTROWS instead of DISTINCTCOUNT, and using a different column as the first argument of the RANKX function, but I have not been able to resolve the issues. Any help or suggestions would be greatly appreciated. Thank you!
