Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I'm quite new with PWBI calculations and I'm wondering if it's possible to substract a value from a previous row to the current row when those are in different columns. Moreover, the calculation should "re-start" for each Route ID.
Here a screenshot to ilustrate the problem I'm handeling:
The new Calculated Field (or Measure) is Time Between Visits (n) = StartDate (n) - FinishDate (n-1), but for each Route ID "re-starts" the calculation, so Time Between Visits should be 0 when Route ID (n) <> Route ID (n-1).
Note: Would you do it as a Calculated Field or a Measure? Is it something to better handle on Power Query Editor?
Thanks in advance for your help,
Anna
Solved! Go to Solution.
Hi @AnnaR ,
You can create a calculated column like this to meet your needs:
Time Between Visits (n)-cc = IF ( MINX ( FILTER ( 'Table', 'Table'[ROUTE ID] = EARLIER ( 'Table'[ROUTE ID] ) ), 'Table'[Index] ) = 'Table'[Index], 0, 'Table'[STARTDATE] - MAXX ( FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ), 'Table'[FINISHDATE] ) )
You can also create a measure as follows:
Time Between Visits (n) -ms = VAR lastindex = CALCULATE ( MAX ( 'Table'[Index] ), FILTER ( ALLSELECTED ( 'Table'[Index] ), 'Table'[Index] < MAX ( 'Table'[Index] ) ) ) VAR Time_Between_Visits = IF ( lastindex <> BLANK (), CALCULATE ( MAX ( 'Table'[STARTDATE] ) ) - CALCULATE ( MAX ( 'Table'[FINISHDATE] ), FILTER ( ALLSELECTED ( 'Table'[Index] ), 'Table'[Index] = lastindex ) ), 0 ) RETURN Time_Between_Visits
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @AnnaR ,
You can create a calculated column like this to meet your needs:
Time Between Visits (n)-cc = IF ( MINX ( FILTER ( 'Table', 'Table'[ROUTE ID] = EARLIER ( 'Table'[ROUTE ID] ) ), 'Table'[Index] ) = 'Table'[Index], 0, 'Table'[STARTDATE] - MAXX ( FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ), 'Table'[FINISHDATE] ) )
You can also create a measure as follows:
Time Between Visits (n) -ms = VAR lastindex = CALCULATE ( MAX ( 'Table'[Index] ), FILTER ( ALLSELECTED ( 'Table'[Index] ), 'Table'[Index] < MAX ( 'Table'[Index] ) ) ) VAR Time_Between_Visits = IF ( lastindex <> BLANK (), CALCULATE ( MAX ( 'Table'[STARTDATE] ) ) - CALCULATE ( MAX ( 'Table'[FINISHDATE] ), FILTER ( ALLSELECTED ( 'Table'[Index] ), 'Table'[Index] = lastindex ) ), 0 ) RETURN Time_Between_Visits
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check
https://community.powerbi.com/t5/Desktop/DAX-Subtract-previous-row-from-current-row/td-p/570742
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |