"m query"
2 Topics
Calculating Current Value using Previous Row's Calculated Value
I have a dataset representing battery state changes, and I need to calculate the ResidualCapacity for each state change. The ResidualCapacity should be calculated as the previous ResidualCapacity plus the current ChargeVariation, without exceeding an UpperBound of 80. Here's the structure of my table with the correct ResidualCapacity value: ChangeState ChargeVariation ResidualCapacity 1 70 70 2 -5 65 3 20 80 4 -10 70 5 -5 65 ChangeState: Index showing the chronological order of battery state changes. ChargeVariation: The amount by which the charge has varied. ChargeVariation < 0: Battery usage ChargeVariation > 0: Battery charging ResidualCapacity: A calculated column that should show the residual battery capacity after each state change. My goal is for ResidualCapacity to always be the previous ResidualCapacity plus the current ChargeVariation, but it should never exceed the UpperBound of 80. I've written the following DAX query, but it returns an incorrect result for the last value of ResidualCapacity (it returns 70 instead of 65): ResidualCapacity = VAR UpperBound = 80 VAR CurrentVariation = 'Table'[ChargeVariation] VAR CurrentState = 'Table'[ChangeState] VAR PervVariationRT = SUMX( FILTER( 'Table', 'Table'[ChangeState] < CurrentState ), 'Table'[ChargeVariation] ) VAR Result = MIN(CurrentVariation + MIN(PervVariationRT, UpperBound), UpperBound) RETURN Result ChangeState ChargeVariation ResidualCapacity (WRONG) 1 70 70 2 -5 65 3 20 80 4 -10 70 5 -5 70 How can I modify this query to correctly calculate the ResidualCapacity calculated column for each state change? It is also OK if the solution is written in M Power QuerySolved1.6KViews1like4Comments
Updating table rows based on earlier row
Hi all, I have a solution that needs to integrate manual updates during downtimes before eventually reconciling the data once the correct information becomes available at source. The solution needs to: Find instances where a flag has indicated a manual adjustment has been made to a user entry Identify any entries associated with this user ID between the date on which the manual adjustment was made and the date on which the source data has been updated to reflect the manual adjustment (if applicable) Apply a specific set of adjustments to the identified rows, dependent on the nature of the adjustment. Considering the attached table, I’ve tried to outline the logic I’m trying to implement below: For the purposes of this explanation, I’ll refer to the manual adjustment as row X. IF Flag = “Adj” ( IF Case = “L” ( For any Date >= Than X.Date WHERE ID = X.ID And Case != X.Case Output = “H” ) IF Case = “M” ( For any Date >= Than X.Date WHERE ID = X.ID And Case != X.Case Level = X.Level ) ) Any advice on how to optimally achieve this within PowerBI would be greatly appreciated. IDCaseFlagLevelDateOutput 1 HC Source 1 03/07/2024 S 1 HC Source 1 04/07/2024 S 1 L Adj 1 05/07/2024 S 1 HC Source 1 06/07/2024 S 1 HC Source 1 07/07/2024 S 1 L Source 1 08/07/2024 S 1 HC Source 1 09/07/2024 S 1 HC Source 1 10/07/2024 S 1 HC Source 1 11/07/2024 S 1 HC Source 1 12/07/2024 S 2 HC Source 3 03/07/2024 S 2 HC Source 3 04/07/2024 S 2 HC Source 3 05/07/2024 S 2 HC Source 3 06/07/2024 S 2 HC Source 3 07/07/2024 S 2 L Source 3 08/07/2024 S 3 HC Source 3 03/07/2024 S 3 HC Source 3 04/07/2024 S 3 HC Source 3 05/07/2024 S 3 HC Source 3 06/07/2024 S 3 HC Source 3 07/07/2024 S 3 L Adj 3 08/07/2024 S 3 HC Source 3 09/07/2024 S 3 HC Source 3 10/07/2024 S 3 HC Source 3 11/07/2024 S 3 HC Source 3 12/07/2024 S 4 HC Source 5 03/07/2024 S 4 HC Source 5 04/07/2024 S 4 L Adj 5 05/07/2024 S 4 HC Source 5 05/07/2024 S 4 HC Source 5 06/07/2024 S 4 HC Source 5 07/07/2024 S 4 HC Source 5 08/07/2024 S 4 HC Source 5 09/07/2024 S 4 HC Source 5 10/07/2024 S 4 HC Source 5 11/07/2024 S 4 L Source 5 12/07/2024 S 5 HC Source 2 03/07/2024 S 5 M Adj 3 04/07/2024 S 5 HC Source 2 04/07/2024 S 5 HC Source 2 05/07/2024 S 5 HC Source 2 06/07/2024 S 5 HC Source 2 07/07/2024 S 5 HC Source 2 08/07/2024 S 5 M Source 3 09/07/2024 S 5 HC Source 3 10/07/2024 S 5 HC Source 3 11/07/2024 S 5 HC Source 3 12/07/2024 S 5 HC Source 3 13/07/2024 SSolved1.2KViews0likes5Comments