Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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! 😊
Solved! Go to Solution.
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 DIFFERENCE
In summary:
If this answer helps you, please give a kudo and mark it as solution 🙂.
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 DIFFERENCE
In summary:
If this answer helps you, please give a kudo and mark it as solution 🙂.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
84 | |
68 | |
63 | |
63 |
User | Count |
---|---|
208 | |
120 | |
113 | |
79 | |
72 |