Forum Discussion
Progressive Difference Calculation
Hello everyone, I would appreciate your assistance in understanding how to set up a measure that allows calculating the revenue difference per customer and order date for each recorded date in Power BI.
To explain further, if we divide customers by invoice date and track their status at precise times, how can we calculate the difference between one observation and the next?
Thank you all for your support! 😊
Hello, I've got the expected result in a matrix using one measure. In that matrix, customer, date, and hour are used as row hierarchy, and in the column of total income we can see the income value at each hour.
M query used to build this sample data:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUTKy1Dcw1jcyMDIBcgzMrAwMgLShAZCK1cGn1NAEotTSlKBKC6ihRtiUGhjqG5ig22+CzXoUlTDrTQmrhFpvDlIZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Delivery Date" = _t, Rel.Date = _t, #"Total Income" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Delivery Date", type text}, {"Rel.Date", type time}, {"Total Income", Int64.Type}}) in #"Changed Type"
Measure:Difference = VAR __CURRENT_ROW_DELIVERY_HOUR = SELECTEDVALUE('Table'[Rel.Date]) VAR __PREVIOUS_HOUR = CALCULATE(MAX('Table'[Rel.Date]), ALLEXCEPT('Table', 'Table'[Customer], 'Table'[Delivery Date]), 'Table'[Rel.Date] < __CURRENT_ROW_DELIVERY_HOUR) VAR __CURRENT_ROW_INCOME = SUM('Table'[Total Income]) VAR __PREVIOUS_ROW_INCOME = CALCULATE(SUM('Table'[Total Income]), 'Table'[Rel.Date] = __PREVIOUS_HOUR) VAR DIFFERENCE = IF(ISBLANK(__PREVIOUS_ROW_INCOME), BLANK(), __CURRENT_ROW_INCOME - __PREVIOUS_ROW_INCOME) RETURN DIFFERENCEIn summary:
- Obtain the hour of the current row (for current customer and delivery date as filtered by the row)
- Obtain the hour of the previous row, which should be the maximum hour value that is smaller than the current hour, for the same customer and delivery date.
- Calculate the income for the current row
- Calculate the income for the previous row (filtered in the current row by the same customer and delivery date, and we also add an additional filter to take exactly the income value of the previous hour)
- Calculate the difference (current row income - previous row income) under the condition that, if there is not a previous hour, just return a blank value and no calculation has to be done.
If this answer helps you, please give a kudo and mark it as solution 🙂.
1 Reply
Hello, I've got the expected result in a matrix using one measure. In that matrix, customer, date, and hour are used as row hierarchy, and in the column of total income we can see the income value at each hour.
M query used to build this sample data:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUTKy1Dcw1jcyMDIBcgzMrAwMgLShAZCK1cGn1NAEotTSlKBKC6ihRtiUGhjqG5ig22+CzXoUlTDrTQmrhFpvDlIZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Delivery Date" = _t, Rel.Date = _t, #"Total Income" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Delivery Date", type text}, {"Rel.Date", type time}, {"Total Income", Int64.Type}}) in #"Changed Type"
Measure:Difference = VAR __CURRENT_ROW_DELIVERY_HOUR = SELECTEDVALUE('Table'[Rel.Date]) VAR __PREVIOUS_HOUR = CALCULATE(MAX('Table'[Rel.Date]), ALLEXCEPT('Table', 'Table'[Customer], 'Table'[Delivery Date]), 'Table'[Rel.Date] < __CURRENT_ROW_DELIVERY_HOUR) VAR __CURRENT_ROW_INCOME = SUM('Table'[Total Income]) VAR __PREVIOUS_ROW_INCOME = CALCULATE(SUM('Table'[Total Income]), 'Table'[Rel.Date] = __PREVIOUS_HOUR) VAR DIFFERENCE = IF(ISBLANK(__PREVIOUS_ROW_INCOME), BLANK(), __CURRENT_ROW_INCOME - __PREVIOUS_ROW_INCOME) RETURN DIFFERENCEIn summary:
- Obtain the hour of the current row (for current customer and delivery date as filtered by the row)
- Obtain the hour of the previous row, which should be the maximum hour value that is smaller than the current hour, for the same customer and delivery date.
- Calculate the income for the current row
- Calculate the income for the previous row (filtered in the current row by the same customer and delivery date, and we also add an additional filter to take exactly the income value of the previous hour)
- Calculate the difference (current row income - previous row income) under the condition that, if there is not a previous hour, just return a blank value and no calculation has to be done.
If this answer helps you, please give a kudo and mark it as solution 🙂.