combinatorics in dinamic dax
Hello guys, i have posted here so many times about getting a logic right, but i just noticed it was impossible, in the way i was thinking, no one could help me. I have a new aproach of how to get the desired result, its by using combinatorics, of course, i need to know if its even possible in the dax. I have the image bellow that shows how the combinations might work, and ahead i also have the code i'm trying, its not working properly and i would like some help with it. In this code, the objective is have at the end the combinations that attend to the condition that the amount of concatenated orders is the highest and the sum of values are <= 21. Obs. this code might work with many different products, but to this example i just used one. Code: FinalCorrectStatus = VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto]) VAR OrdersTable = FILTER(ALL('DB_ORDERS'), 'DB_ORDERS'[Produto] = CurrentItem) VAR OrderCombinations = ADDCOLUMNS( GENERATEALL( OrdersTable, SUMMARIZE( OrdersTable, 'DB_ORDERS'[Pedido], "Soma_Quant_Falta", SUM('DB_ORDERS'[QT. falt]) ) ), "PedidosConcatenados", CONCATENATEX(OrdersTable, 'DB_ORDERS'[Pedido], ", "), "Qtd_Pedidos", COUNTROWS(OrdersTable) -- Conta o número de pedidos na combinação ) VAR FilteredCombinations = FILTER(OrderCombinations, [Soma_Quant_Falta] <= 21) VAR MaxPedidos = MAXX(FilteredCombinations, [Qtd_Pedidos]) -- Encontra o maior número de pedidos VAR BestCombination = FILTER(FilteredCombinations, [Qtd_Pedidos] = MaxPedidos) -- Mantém apenas a combinação com mais pedidos RETURN BestCombination Can anyone please help me with it?Impossible dax code sum
Hello, I've been here for a while trying to find out what was the problem with my dax code; i just found it, but its seems to be impossible of doing, i need the stock of the itemns to be distributed correctly following the order number order, from the lowest to the bigest. in the code i used, the problem is that when the 'DB_ORDERS'[Quant. Falta] <= StockAvailable is true, the sum will ocour, but its not right, i need a way to verify if the currend cumulated value of the sum is bigger than the current stock, if it is, it will not sum the current item quantity, and it will set the current item as out.. it seems to be impossible of doing this.. can anyone help? file: https://1drv.ms/u/c/41783bdbd58abfd0/Ee4LSpTOV-pApa7mDD9DCAcBYoZEYqhFykemlsTpfg0jwQ?e=WlkIMu the code is: ItemStatus = VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto]) VAR CurrentOrderNum = SELECTEDVALUE('DB_ORDERS'[Pedido]) VAR StockAvailable = CALCULATE( SUM('Table Estoq'[Estoque Real]), 'Table Estoq'[Produto] = CurrentItem ) VAR RunningStockVerify = SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Quant. Falta] <= StockAvailable && 'DB_ORDERS'[Produto] = CurrentItem ), 'DB_ORDERS'[Quant. Falta] ) VAR RunningStock = SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && RunningStockVerify <= StockAvailable && 'DB_ORDERS'[Produto] = CurrentItem ), 'DB_ORDERS'[Quant. Falta] ) //RETURN // RunningStockVerify RETURN IF( MAX('DB_ORDERS'[Quant. Falta]) - RunningStockVerify <= 0 && RunningStock <> BLANK(), "available", "out" ) I have 21 of stock available, i need it to be distributed throught the marked itens
Forecast percentage from measure
Hi, I am trying to create a forecast model, that will have a parameter, which holds the return on equity value, which can be changed dynamically for forecasting purposes. The challenge is that I have a measure which is calculating the cumulative return (%) and then for the last value a formula should be applied to calculate a simple forecast based on the latest value. The formula used to forecast is [Latest value from measure] * (1+Return on Equity)^t Please see the example from Excel as per below. Does anyone know how to solve this in PowerBI DAX? I have a data table containing both workdays and weekends however days from today should only count workdays. Let me know if anything else is required from me to solve this. Best Regards,Why do my SWITCH measure results have a long number despite selecting 2 decimals?
To give a bit more info, this is the main part of how I insert the values into the SWITCH measure. Please note it's a very shortened version, but includes the two main types of values (with and without additional text) var delta_etc_pfe_perc = IF( LEN([Δ ETC vs PFE %]) > 0, [Δ ETC vs PFE %], 0 ) var result = SWITCH( TRUE(), /* Metric = Days */ metrics = 1 && selected_column_1 = "Planned", [Frc Days], metrics = 1 && selected_column_1 = "Δ ETC vs PFE", [Δ ETC vs PFE] & " (" & delta_etc_pfe_perc & ")", So in this case, my value Delta ETC vs PFE is numerical, but the variable I insert after is text because SWITCH doesn't allow me to insert numerical percentage variables, as they will become reformatted as non percentage. So I format as text the measure as I calculate it on its own as follows: FORMAT(Result, "0.00%") and then here make that if() call on it As you can see I also added a measure to conditionally colour only some of the values, and I just want to state beforehand that changing that won't affect the result. Why do my SWITCH measure results look like this? I already fixed the decimal number to 2 values. It only happens for those rows where I stated that it should give me both the result of an operation and then another one within parenthesis. But both of those values I also fixed to 2 decimals only
Semi-additive max date by account and category
Hi all, I am working with data which captures stages of clients from prospect to customer. each stage records a different potential contract value of the customer, and the latest date for particular customer holds the most accurate value I want to be able to extract by date/month etc. Link to dummy data in PBI for context: https://1drv.ms/u/s!AoIOEd5cDuqQuRmioxhdXlsHT3pX?e=72Nwue This is the sample data screenshot and desired output (count total max value of opp based on the last date the opp has been modified and so on for the count of opps based on stage and forecast category) I tried using Semi-Additive Measure to calculate sum for Last date of the day, but it only sums max opp_1 and max_date - It does not add multiple opp values to create pipeline of prospects&customers based on any given time period (month date) - see below Any help would be appreciated. 🙂
