Age of user - calculated column Power Query
Hello. I need support regarding user age calculation. I have the following data and would like to create a calculated column in PowerQuery that shows: User_Start_DT USER_END_DT Age column 21/06/2019 null ( still working ) 0 - 5 Years 11/07/2007 11/07/2007 0 - 5 Years 11/09/2013 30/09/2021 5 - 10 Years 28/05/2009 23/03/2015 5 - 10 Years 21/06/2011 11/01/2022 10 - 15 Years 11/07/2007 null ( still working ) 10 - 15 Years etc
New Conditional Column Calculation based on (non-value) other column entry
Hi, I'm relatively new to PBI and I wanted to create a column showing the 'Bonus' amount (the new 'outcome' column) but based on a calculation of either ("Sales Rev" x "Multiplier") or ("Sales+Referrals Rev" x "Multiplier)". So for example, if the entry in Column B ("Level") = 'Junior', the multiply column C ("Sales Rev" which is itself a sum of certain columns) by the column E ("Multiplier", which is itself a calculated field). But if the entry in Column B("Level") = 'Exec', the multiply column D ("Sales Rev + Referrals" which is itself a sum of different columns) by the column E ("Multiplier"). Hope that makes sense. I'm working in the Transform Data table at the moment, and would appreciate any guidance! I've copied the data back into excel to illustrate what i'm trying to show... Much appreciated, Bx
Create column based on difference between two dates from two tables
Hi, I am trying to create a Calculated column to track the difference between two dates from two different columns. The goal is to find when a shipment has been recieved (Final Merger) and when the product has been recieved and put away (Export Worksheet). I am trying to create a calcuated column using an if statement, however i run into the error. The If statement is created within Export OR Final Merger 1. Wont allow me to select multiple tables in my If Statement. If I select either fields, I wont be able to select the other table. 2. Error - "Cannot Find (Column Name) Tables Table Column Type Final_Merger First Date Date Export Worksheet Second Date Date Date Dimension Date Date References First Table Second Table Column Relationship Final_Merger Export Worksheet Receipt ID Many to Many Final Merger Date Dimension Date Many to OneHow to calculate column based on Slicer
Hi everyone, I have a table like the next one: ID Class Points Points_Norm 1 A 50 0 2 A 800 0.1372455 3 A 911 0.15755784 4 B 456152 83.4639301 5 B 1521 0.26918418 6 B 123 0.01335856 7 C 14894 2.71636296 8 C 4561 0.82548594 9 C 546516 100 10 C 146516 26.8023994 Also, I have a slicer for the column "Class". My objective is to put the variable "Points_norm "in a graph (it is just the values of "Points" normalized between 0 and 100). What I'm looking for is that, when I select one of the classes with my slicer, I get the normalized values just for the filtered results. In this example, if I select Class==A with the slicer, what I would get is: ID Class Points Points_Norm 1 A 50 0 2 A 800 87.1080139 3 A 911 100 Because the normalization is done just for those values I calculated. Does anyone know how to get this in Power BI? Thank you all
Compare two columns of a table and create a derived column based on comparison in DAX Measure
I need to compare two columns of a table (not a physical table but created in a DAX measure) and derive another column based on the comparison result. Can some one help with how this can be achieved within a DAX measure. My data is in below format : But above table is not a physical table but a table created in DAX measure using below code : = ADDCOLUMNS ( 'OnTimeTable', "RankByOrderDate", RANKX ( CALCULATETABLE ( 'OnTimeTable', FILTER ( 'OnTimeTable', 'OnTimeTable'[Customer Continent] = EARLIER ( 'OnTimeTable'[Customer Continent] ) && 'OnTimeTable'[Customer Country] = EARLIER ( 'OnTimeTable'[Customer Country] ) && 'OnTimeTable'[Product Category] = EARLIER ( 'OnTimeTable'[Product Category] ) ) ), 'OnTimeTable'[Order Date], , ASC ), "RankByDeliveryDate", RANKX ( CALCULATETABLE ( 'OnTimeTable', FILTER ( 'OnTimeTable', 'OnTimeTable'[Customer Continent] = EARLIER ( 'OnTimeTable'[Customer Continent] ) && 'OnTimeTable'[Customer Country] = EARLIER ( 'OnTimeTable'[Customer Country] ) && 'OnTimeTable'[Product Category] = EARLIER ( 'OnTimeTable'[Product Category] ) ) ), 'OnTimeTable'[Delivery Date], , ASC ) ) Both the RankByOrderDate and RankByDeliveryDate columns are created by Ranking the table data based on Order and Delivery Dates respectively, within a group of Continent,Country and Product Category. Comparing these two rank columns, a new column[DeliveryAsPerQueueOrder, shown in sample data] needs to be created with the below logic : If the two columns being compared are equal then assign 1 else 0. Finally I will need to SUM the value of this derived column (this part is later first focus is on getting the column with desired values). All this functionality needs to be done in a DAX measure, without using a calculated column or calculated table.Subtracting Calculated Columns from Each Other
I have absence data: ID Start End Duration Days in Month Working Days Leave Entitlements Days Available Absence Net 727 02/01/2019 07/01/2019 6 31 23 2.5 20.5 6 14.5 727 10/03/2020 11/03/2020 2 31 22 2.5 19.5 2 17.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 7 -48.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 31 -48.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 28 -48.5 Where I'm subtracting [END] - [START] = Duration and then subtracting the ABSENCE from this. However for periods longer than 1 month it groups the total (66 days) rather than individual days (7,31,28) is there a way to get it so the chart is like: ID Start End Duration Days in Month Working Days Leave Entitlements Days Available Absence Net 727 02/01/2019 07/01/2019 6 31 23 2.5 20.5 6 14.5 727 10/03/2020 11/03/2020 2 31 22 2.5 19.5 2 17.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 7 10.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 31 -13.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 28 -10.5 PBIX attached for better explanation . https://www.dropbox.com/s/zjizlyun1nuur15/Sample%20Absence.pbix?dl=0
SUM of MAX values in SUMMARIZE function
Hi! I think i'm missing some DAX function, i have this code for a calculated table: SUMMARIZE('produccion chat y ret','produccion chat y ret'[C-OP],'produccion chat y ret'[N-COLADA-O],'produccion chat y ret'[W-Q-TONELA-O],"fecha mov",MAX('produccion chat y ret'[Fecha MOVTO])) and is returning me this table (filtered to be more clear): and i wanted to SUM the tons for the MAX date of my table so i tried this code: SUMMARIZE('produccion chat y ret','produccion chat y ret'[C-OP],'produccion chat y ret'[N-COLADA-O],"fecha mov",MAX('produccion chat y ret'[Fecha MOVTO]),"tns",SUM('produccion chat y ret'[W-Q-TONELA-O])) but it's returning me the total sum: it should only sum the first two rows with date of 26/08, how can i reach this result? Thank you for your help!
