Concatenate text by group : EARLIER function not working
Hello everyone, I am trying to create a measure in DAX to concatenate all CODES that have the same Group ID. I have tried the CONCATENATEX function mentioned in many other discussion threads but it doesn't seem to function as there appears to be a problem with the function EARLIER which doesn't recognize any value. This is the initial table: Group Registration ID SPN CODE 578-24-3545895 SM-CC 578-24-3545895 WR-SF 578-24-3544933 FU-TR 578-24-3544933 SM-AD 578-24-3544933 SM-CC 578-24-3544933 WR-SF 578-24-3532081 WR-SF 578-24-3524650 SM-DP 578-24-3518403 LP-MS 578-24-3518403 N/A The desired output should be as such: 578-24-3545895 SM-CC SM-CC; WR-SF 578-24-3544933 FU-TR FU-TR; SM-AD; SM-CC; WR-SF 578-24-3532081 WR-SF WR-SF 578-24-3524650 SM-DP SM-DP 578-24-3518403 LP-MS LP-MS; N/A I have tried this but it's not working and I have the following error : CODES = CONCATENATEX(FILTER(SPN, SPN[Group Registration ID]=EARLIER(SPN[Group Registration ID])), SPN[SPN CODE], "; ") I would appreciate any orientation Thank you
How to replace values based on another cell of another column
I have the below data with trade, stock and price column where the output price column should be If Trade= "BUY" then Price corresponding to BUY Else if Trade= "SELL" then Price corresponding to BUY in all sell rows So basically the output for all the trades should be the Price for BUY trade i.e. 2000 in our example
Determine the First of Two Actions
Hello, I have a table (‘1egh TSQL’) with columns of specific actions and dates on which those actions occurred. I used the formula below to calculate a new column called Fiscal Year, based on the following criteria: Must be the MAX Sequence for the project Must be the FIRST of the *Action “Application Finally Rejected” or “Application Approved” (whichever one occurs first) If the first two criteria apply and the *Date is 10/01/2020 – 09/30/2021, then Fiscal Year = “FY21” If the first two criteria apply and the *Date is 10/01/2021 – 09/30/2022, then Fiscal Year = “FY22” If the first two criteria apply and the *Date is 10/01/2022 – 09/30/2023, then Fiscal Year = “FY23” If nothing applies, then BLANK How can I correct my formula to only select the first action that occurs? In this example, FY22 should be listed next to the *Action “Application Finally Rejected”. Fiscal Year = VAR _sequence = '1egh TSQL'[Sequence] VAR _pjtnumber = '1egh TSQL'[FHA Number] VAR _action = '1egh TSQL'[*Action] VAR _table = FILTER ( '1egh TSQL', '1egh TSQL'[FHA Number] = _pjtnumber ) VAR _maxseq = MAXX ( _table, '1egh TSQL'[Sequence] ) RETURN IF ( _sequence = _maxseq && ( _action = "Application Finally Rejected" || _action = "Application Approved" ), SWITCH ( TRUE (), '1egh TSQL'[*Date] >= DATE ( 2020, 10, 1 ) && '1egh TSQL'[*Date] <= DATE ( 2021, 9, 30 ), "FY21", '1egh TSQL'[*Date] >= DATE ( 2021, 10, 1 ) && '1egh TSQL'[*Date] <= DATE ( 2022, 9, 30 ), "FY22", '1egh TSQL'[*Date] >= DATE ( 2022, 10, 1 ) && '1egh TSQL'[*Date] <= DATE ( 2023, 9, 30 ), "FY23", BLANK () ), BLANK () )
Create Calculated Column for new security trades which shows either current holdings, buys, sells
Hi Guys - newbie here, i am trying to add a column which identifies new trades between two dates (current week and prior week) with the goal to calculate the total value of new trades , sells, and existing holdings. My data set looks something like this (Type of Holding is what I want the calculated column to look like) Link to sample dataset: https://docs.google.com/spreadsheets/d/e/2PACX-1vQQCHpm7AAomMcuPlExXjY3tYnj7WzLkiZXfPUvL6FHtzMUc-untTIlfSNzMNBIL9BLZSbvaNXu7NVG/pubhtml I would like to use this column to track the changes in face value using a decomposition tree (something similar to the below example).
