cumulative sum

32 Topics

Resetting my cumulative total when it dips below zero
Hi I have a cumulative sum that has a starting total which then needs to be adding a Loadingtotal. this loading total could be positive or negative. if the cumulitive sum goes below zero it should reset and start adding the loading total as if it was starting again but at zero. I have the following as an example Any help would be appreciated
Solved
zbrooks
3 months ago Place DAX Commands and Tips
1.3KViews
1like
12Comments
Conditional and selective sum with filters
Hey guys! how you doing? Just got an insight in my dax code, i've been working on the logic of it for a while and i think im getting somewhere, but still need help. I need the code to return the "out" and "available" status correctly for each order, in a way that it will always start with the earliest order then to the oldest. the reult i got for this actual data is right, but it changes when i change the values, getting wrong results, here is an example of the right result i got.: I have used many calculations and contitions to achieving this result, but the logic is still not complete. look at the result i get when change the values: The correct results for this last one would be available, out, available, out, out, available, out, out. The measure i am using is the "FinalCorrectStatus" you can find it in the file: DistributeStok The code is: FinalCorrectStatus = VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto]) VAR CurrentOrderNum = SELECTEDVALUE('DB_ORDERS'[Pedido]) VAR StockAvailable = CALCULATE( SUM('Table Estoq'[Estoque Real]), 'Table Estoq'[Produto] = CurrentItem ) //soma normal soma sem condição cumulativamente VAR SomaNormal = SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem ), 'DB_ORDERS'[Quant. Falta] ) //Filtro de apoio para a SomaMaiorEstoq VAR SomaMaiorApoio = FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem ) //soma apenas quando o valor da "SomaNormal" for maior que o estoque VAR SomaMaiorEstoq = SUMX( ADDCOLUMNS( SomaMaiorApoio, "@Acumulado", SUMX( FILTER( SomaMaiorApoio, 'DB_ORDERS'[Pedido] <= EARLIER('DB_ORDERS'[Pedido]) ), 'DB_ORDERS'[Quant. Falta] ) ), VAR TotalAcumulado = [@Acumulado] RETURN IF(TotalAcumulado > StockAvailable, 'DB_ORDERS'[Quant. Falta], 0) ) //diferenca entre A soma cumulativa normal e a soma apenas dos maiores que o estoque VAR Diferenca = SomaNormal - SomaMaiorEstoq //valor resultante de "Diferenca" + a quantidade do item no pedido VAR Dif_mais_QTFALT = Diferenca + SELECTEDVALUE('DB_ORDERS'[Quant. Falta]) //verifica se o estoque já atingiu seu limite, vai somando o que estiver dentro da condição de caber no estoque VAR SomaFinal = SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem && ( SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem ), 'DB_ORDERS'[Quant. Falta] ) <= StockAvailable || (Diferenca + 'DB_ORDERS'[Quant. Falta]) <= StockAvailable ) ), 'DB_ORDERS'[Quant. Falta] ) RETURN IF( ISBLANK(StockAvailable), "out", IF( SomaFinal <= StockAvailable && (Dif_mais_QTFALT <= StockAvailable), "available", "out" ) ) its very complex, and i am almost giving up.. Can anyone help me please? Thankyou
ClaudioF
1 year ago Place DAX Commands and Tips
1.1KViews
0likes
5Comments
Complex conditional summing
Hey everyone! how you doing? I have been now a month trying to make this logic work in dax but no solution. Cna anyone please help me with this? or even just say that power Bi will not be able of doing it happen? Here is the thing: I need to distribute evenly the stock of an item throught the orders of this item, in a way that the result is as shown in the table bellow: I need the stock to be distributed in a way that when its not enought to the order it will try to fit in the other order until the total stock is used. Use the stock amount of 21 for this example, you will see that the ones set as available, when summed will be exactly 21. The status column is the outcome that i need from the dax code. This is very hard, i don't even know if its possible; can anyone please helpe me with it? Thankyou a lot allready.
ClaudioF
1 year ago Place DAX Commands and Tips
10KViews
0likes
10Comments
running total goes wrong
Hi Struggling for some time now in this issue. Would like to have a running sum on measure delta like matrix below. Delta works fine. is based on measure 1 and 2. Measure 2 is special: data depends on calendar!YearMonth: data previous month (202504) is something else compared to current month and later YearMonth are also columns in matrix. Running total measure goes wrong. It adds up measure1 instead of measure delta. Any suggestions ? Should be 202504 202505 202506 202507 202508 202509 202510 202511 202512 measure1 100 100 100 100 100 100 100 100 100 measure2 50 60 60 60 60 60 60 60 60 measure delta 50 40 40 40 40 40 40 40 40 running total 50 90 130 170 210 250 290 330 370 But is 202504 202505 202506 202507 202508 202509 202510 202511 202512 measure1 100 100 100 100 100 100 100 100 100 measure2 50 60 60 60 60 60 60 60 60 measure delta 50 40 40 40 40 40 40 40 40 running total 50 150 250 350 450 550 650 750 850 measure2 = if( SELECTEDVALUE('Calendar'[YearMonth]) >= VALUE(CONCATENATE(YEAR(now()), FORMAT(MONTH(now()),"00")) ), [measure_X] , [measure_Y] ) measure delta = measure1 - measure2 running total = CALCULATE( [measure delta], FILTER( ALLSELECTED('Calendar'[YearMonth]), 'Calendar'[YearMonth] <= MAX('Calendar'[YearMonth]) ) )
Solved
BIuser09
1 year ago Place DAX Commands and Tips
739Views
0likes
3Comments
Cumulative total days active product
Hi all, Been struggeling with this challenge: Per month, I need to see the average of active days per product. Example, 1 product gets active on 1-jan-2024, and gets deactivated on 1-oct-2024. So on 10-jan-2024 the average of active days = 10, on 30-9-2024, the average of active days = 274. On 1-10-2024 its NULL for this product. This is for 1 product, but it needs to aggregate for all products active on that day. For the convenience I mad a fact table (fact_ActiveProducts) with 1 row per product per active day. fact_ActiveProducts, columns: ProductID ,DateID (related with Calendar table) ,BeginDate (date format) ,EndDate (dateformat) (can be empty when still active). ,NumberOfActiveDays (1, a hard number 1 on each row to easy SUM the column). Anybody got suggestions? The usual ways to calculate a rolling sum of cumulative total still counts the days of products after they where deactivated. And after the date that they are deactivated, they shouldn't be counted anymore for those dates. If I get the total count of days per row, I can divide it by a distinctcount of products active on that day. The next step is to create an average by month, or get the value of the maxdate for the selected month.
Solved
Victornld2
1 year ago Place DAX Commands and Tips
770Views
0likes
3Comments
Cumulative reset after reaching sales target
Hello everyone, I have sales data set containing sales amount of every stores in 3 different regions. Each region has their own sales target (column Target Sales), I want to calculate a cumulative sum of sales amount such that it is resetted once the target is reached or almost reached. I tried using grouping method but do not get 100% desired result. For example, for the South region, the 'Reset cumulative' works perfectly until group 3. Group 1 includes 0.29 + 8.57 + +
SailorMoon98
2 years ago Place DAX Commands and Tips
1KViews
0likes
4Comments
Measure for cumulative sum
Hi, I have some trouble with creating a measure. I have the fields below from 2 tables. Date and MonthYear from a calendar table. The other columns are from a different table with obviously a relationship between Date columns. Now I am trying to create a cumulative sum measure of the "Hours to do per workday". But this measure should be able to filter on a speficic Category, Team and Project. But when removing a (for example) Team filter it should cumulate the sum per Category. And when removing the Category and Team it should cumulate the sum per Date. Etc. So on the 09/07/2024 date it should be the sum of 08/07/2024 and 09/07/2024 together. For the Categories, Teams and Projects. How can this be done in a measure? Is anyone able to help me? Row Labels8-7-20249-7-202410-7-2024Grand TotalCategory A0,900,900,902,70Team A0,900,900,902,70Category B1,651,651,654,95Team D0,850,850,852,54Team I0,800,800,802,41Category C6,716,715,9319,35Team B1,751,750,974,47Team C0,760,760,762,29Team E1,761,761,765,27Team F0,840,840,842,52Team G0,800,800,802,40Team H0,800,800,802,40Grand Total9,269,268,4827,00 GH761008 0,90 0,90 0,90 2,70 GH760640 0,85 0,85 0,85 2,54 AT511552 0,80 0,80 0,80 2,41 AT511637 0,97 0,97 0,97 2,91 GH760885 0,78 0,78 1,56 AT510847 0,76 0,76 0,76 2,29 GH760243 0,90 0,90 0,90 2,70 GH760809 0,86 0,86 0,86 2,57 GH760619 0,84 0,84 0,84 2,52 GH760854 0,80 0,80 0,80 2,40 GH760556 0,80 0,80 0,80 2,40
Solved
JC2022
2 years ago Place DAX Commands and Tips
515Views
0likes
1Comment
Cumulative sum measure
Hi, I have some trouble with creating a measure. I have the fields below from 2 tables. Date and MonthYear from a calendar table. The other columns are from a different table with obviously a relationship between Date columns. Now I am trying to create a cumulative sum measure of the "Hours to do per workday". But this measure should be able to filter on a speficic Category and Team. But when removing a (for example) Team filter it should cumulate the sum per Category. And when removing the Category and Team it should cumulate the sum per Date. Etc. Is anyone able to help me? Current fields: Date MonthYear Category Team Project Hours to do per workday 8-7-2024 jul-24 Category A Team A GH761008 0,90 8-7-2024 jul-24 Category B Team I AT511552 0,80 8-7-2024 jul-24 Category B Team D GH760640 0,85 8-7-2024 jul-24 Category C Team C AT510847 0,76 8-7-2024 jul-24 Category C Team B AT511637 0,97 8-7-2024 jul-24 Category C Team E GH760243 0,90 8-7-2024 jul-24 Category C Team H GH760556 0,80 8-7-2024 jul-24 Category C Team F GH760619 0,84 8-7-2024 jul-24 Category C Team E GH760809 0,86 8-7-2024 jul-24 Category C Team G GH760854 0,80 8-7-2024 jul-24 Category C Team B GH760885 0,78 9-7-2024 jul-24 Category A Team A GH761008 0,90 9-7-2024 jul-24 Category B Team I AT511552 0,80 9-7-2024 jul-24 Category B Team D GH760640 0,85 9-7-2024 jul-24 Category C Team C AT510847 0,76 9-7-2024 jul-24 Category C Team B AT511637 0,97 9-7-2024 jul-24 Category C Team E GH760243 0,90 9-7-2024 jul-24 Category C Team H GH760556 0,80 9-7-2024 jul-24 Category C Team F GH760619 0,84 9-7-2024 jul-24 Category C Team E GH760809 0,86 9-7-2024 jul-24 Category C Team G GH760854 0,80 9-7-2024 jul-24 Category C Team B GH760885 0,78 10-7-2024 jul-24 Category A Team A GH761008 0,90 10-7-2024 jul-24 Category B Team I AT511552 0,80 10-7-2024 jul-24 Category B Team D GH760640 0,85 10-7-2024 jul-24 Category C Team C AT510847 0,76 10-7-2024 jul-24 Category C Team B AT511637 0,97 10-7-2024 jul-24 Category C Team E GH760243 0,90 10-7-2024 jul-24 Category C Team H GH760556 0,80 10-7-2024 jul-24 Category C Team F GH760619 0,84 10-7-2024 jul-24 Category C Team E GH760809 0,86 10-7-2024 jul-24 Category C Team G GH760854 0,80 Desired end result:
Solved
JC2022
2 years ago Place DAX Commands and Tips
734Views
0likes
3Comments
DAX Cumulative Total incremented by this total
Hi, Has anyone a idea how to solve this situation in DAX. In Excel it is quite easy becasue you can always refer to a specific cell. I need to increment the A Value by B Value, and if A is Blank the A increment should happen by the previous result of the calculation. Thank You in advance!
Art666
2 years ago Place DAX Commands and Tips
711Views
0likes
3Comments
Cumulative Sum column. Resets if row value = 0
Hello, I am trying to simulate row iteration, suming al values = 1 given certain filters. I have the several columns: # FY: Can be FY1920, FY2021, and so on... # RED: Can be only "2G, 3G, 4G" # Entorno: Total, Urbano and Rural. # Cluster: More than 30 region values (Madrid, Barcelona ...) # WEEK: W01, W02 ... # IN_LAST_8W: 1 if row in last 8weeks, else 0. # Incumple_dispo: 1 or 0 I need to calculate a column that sums all rows where Incumple_dispo=1, given some filters, but if Incumple_dispo=0, the sum resets. I though I could achieve this with a cumulative column, but i cant... # CLUSTER_CUENTA = CALCULATE ( SUM ( '### DWT_Week_Cluster_combinada_acc'[# Incumple_dispo] ); FILTER ( ALL ( '### DWT_Week_Cluster_combinada_acc' ); '### DWT_Week_Cluster_combinada_acc'[ENTORNO] = EARLIER ( '### DWT_Week_Cluster_combinada_acc'[ENTORNO] ) && '### DWT_Week_Cluster_combinada_acc'[# RED] = EARLIER ( '### DWT_Week_Cluster_combinada_acc'[# RED] ) && '### DWT_Week_Cluster_combinada_acc'[# CLUSTER] = EARLIER ( '### DWT_Week_Cluster_combinada_acc'[# CLUSTER] ) && '### DWT_Week_Cluster_combinada_acc'[# IN_LAST_8W] = 1 && '### DWT_Week_Cluster_combinada_acc'[# FY_WEEK_NUM] <= EARLIER ( '### DWT_Week_Cluster_combinada_acc'[# FY_WEEK_NUM] ) ) ) can you give me a hint? thanks in advance.
Anonymous
2 years ago Place DAX Commands and Tips
3.2KViews
0likes
2Comments