Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
109 | |
102 | |
85 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |