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.
Hi,
I have a matrix report below format.
Value 4 = Value1+Value2-Value3
if start value1 is 0 I need to show previous value4. I tried using lookup in if condition getting circular dependency. Any help would appreciate it.
Data
Item | DATE | Value1 | Value2 | Value3 | Month End Inventory |
K60710 | 07/01/2020 0:00 | 4503 | 0 | 205 | 4298 |
K60710 | 08/01/2020 0:00 | 0 | 1045 | 240 | 805 |
K60710 | 09/01/2020 0:00 | 0 | 1662 | 1080 | 582 |
K60710 | 10/01/2020 0:00 | 0 | 3044 | 1450 | 1594 |
K60710 | 11/01/2020 0:00 | 0 | 1810 | 5816 | -4006 |
K60710 | 12/01/2020 0:00 | 0 | 439 | 2800 | -2361 |
K60710 | 01/01/2021 0:00 | 0 | 414 | 697 | -283 |
K60710 | 02/01/2021 0:00 | 0 | 1167 | 425 | 742 |
K60710 | 03/01/2021 0:00 | 0 | 1148 | 1560 | -412 |
K60710 | 04/01/2021 0:00 | 0 | 656 | 2050 | -1394 |
K60710 | 05/01/2021 0:00 | 0 | 774 | 1513 | -739 |
K60710 | 06/01/2021 0:00 | 0 | 535 | 909 | -374 |
K60710 | 07/01/2021 0:00 | 0 | 496 | 996 | -500 |
K60710 | 08/01/2021 0:00 | 0 | 1158 | 841 | 317 |
K60710 | 09/01/2021 0:00 | 0 | 619 | 1688 | -1069 |
K60710 | 10/01/2021 0:00 | 0 | 3822 | 1775 | 2047 |
K60710 | 11/01/2021 0:00 | 0 | 1648 | 5396 | -3748 |
K60710 | 12/01/2021 0:00 | 0 | 479 | 2392 | -1913 |
K6PS64 | 07/01/2020 0:00 | 916 | 0 | 0 | 916 |
K6PS64 | 08/01/2020 0:00 | 0 | 0 | 0 | 0 |
K6PS64 | 09/01/2020 0:00 | 0 | 0 | 0 | 0 |
K6PS64 | 10/01/2020 0:00 | 0 | 0 | 0 | 0 |
K6PS64 | 11/01/2020 0:00 | 0 | 0 | 0 | 0 |
K6PS64 | 12/01/2020 0:00 | 0 | 0 | 0 | 0 |
K6PS64 | 01/01/2021 0:00 | 0 | 0 | 178 | -178 |
K6PS64 | 02/01/2021 0:00 | 0 | 196 | 204 | -8 |
K6PS64 | 03/01/2021 0:00 | 0 | 290 | 284 | 6 |
K6PS64 | 04/01/2021 0:00 | 0 | 348 | 366 | -18 |
K6PS64 | 05/01/2021 0:00 | 0 | 376 | 377 | -1 |
K6PS64 | 06/01/2021 0:00 | 0 | 209 | 226 | -17 |
K6PS64 | 07/01/2021 0:00 | 0 | 229 | 244 | -15 |
K6PS64 | 08/01/2021 0:00 | 0 | 320 | 275 | 45 |
K6PS64 | 09/01/2021 0:00 | 0 | 285 | 294 | -9 |
K6PS64 | 10/01/2021 0:00 | 0 | 401 | 343 | 58 |
K6PS64 | 11/01/2021 0:00 | 0 | 374 | 415 | -41 |
K6PS64 | 12/01/2021 0:00 | 0 | 210 | 320 | -110 |
K6PS94 | 07/01/2020 0:00 | 831 | 0 | 0 | 831 |
K6PS94 | 08/01/2020 0:00 | 0 | 0 | 0 | 0 |
K6PS94 | 09/01/2020 0:00 | 0 | 0 | 0 | 0 |
K6PS94 | 10/01/2020 0:00 | 0 | 73 | 0 | 73 |
K6PS94 | 11/01/2020 0:00 | 0 | 369 | 315 | 54 |
K6PS94 | 12/01/2020 0:00 | 0 | 224 | 335 | -111 |
K6PS94 | 01/01/2021 0:00 | 0 | 313 | 247 | 66 |
K6PS94 | 02/01/2021 0:00 | 0 | 272 | 254 | 18 |
K6PS94 | 03/01/2021 0:00 | 0 | 393 | 298 | 95 |
K6PS94 | 04/01/2021 0:00 | 0 | 517 | 384 | 133 |
K6PS94 | 05/01/2021 0:00 | 0 | 435 | 574 | -139 |
K6PS94 | 06/01/2021 0:00 | 0 | 226 | 389 | -163 |
K6PS94 | 07/01/2021 0:00 | 0 | 324 | 245 | 79 |
K6PS94 | 08/01/2021 0:00 | 0 | 424 | 360 | 64 |
K6PS94 | 09/01/2021 0:00 | 0 | 627 | 340 | 287 |
K6PS94 | 10/01/2021 0:00 | 0 | 593 | 702 | -109 |
K6PS94 | 11/01/2021 0:00 | 0 | 459 | 489 | -30 |
K6PS94 | 12/01/2021 0:00 | 0 | 3 | 545 | -542 |
Hi @Anonymous ,
Please do like this.
1. Unpivot the three columns.
2. Create a measure like this.
Measure =
VAR v1 =
CALCULATE(
MAX('Sheet1 (2)'[Value]),
FILTER( 'Sheet1 (2)', 'Sheet1 (2)'[Attribute] = "Value1")
)
VAR v2 =
CALCULATE(
MAX('Sheet1 (2)'[Value]),
FILTER( 'Sheet1 (2)', 'Sheet1 (2)'[Attribute] = "Value2")
)
VAR v3 =
CALCULATE(
MAX('Sheet1 (2)'[Value]),
FILTER( 'Sheet1 (2)', 'Sheet1 (2)'[Attribute] = "Value3")
)
VAR v4_1 = v1 + v2 - v3
VAR v = MAX('Sheet1 (2)'[Value])
RETURN
IF(
HASONEFILTER( 'Sheet1 (2)'[Attribute] ),
v,
IF(
v1 <>0,
v4_1,
CALCULATE(
MAX('Sheet1 (2)'[Month End Inventory]),
FILTER(
ALL('Sheet1 (2)'),
'Sheet1 (2)'[DATE] < MAX('Sheet1 (2)'[DATE]) && 'Sheet1 (2)'[Item] = MAX('Sheet1 (2)'[Item]) && 'Sheet1 (2)'[Attribute] = MAX('Sheet1 (2)'[Attribute])
)
)
)
)
3. Change the name of the row subtotal label.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply. How can I place value 4 in value1 if value 1 0 and again v4=v1+v2-v3. For example in values second column has 0 in value1 I need put previous value4 4296 in value1 and v4 calculation repeats as v1+v2-v3
Hi @Anonymous ,
Is this?
Measure 2 =
VAR v1 =
CALCULATE(
MAX('Sheet1 (2)'[Value]),
FILTER( 'Sheet1 (2)', 'Sheet1 (2)'[Attribute] = "Value1")
)
VAR v2 =
CALCULATE(
MAX('Sheet1 (2)'[Value]),
FILTER( 'Sheet1 (2)', 'Sheet1 (2)'[Attribute] = "Value2")
)
VAR v3 =
CALCULATE(
MAX('Sheet1 (2)'[Value]),
FILTER( 'Sheet1 (2)', 'Sheet1 (2)'[Attribute] = "Value3")
)
VAR total1 = v1+v2-v3
VAR v1_new=
CALCULATE(
MAX('Sheet1 (2)'[Month End Inventory]),
FILTER( ALL('Sheet1 (2)'), 'Sheet1 (2)'[DATE] < MAX('Sheet1 (2)'[DATE]) && 'Sheet1 (2)'[Item] = MAX('Sheet1 (2)'[Item]) && 'Sheet1 (2)'[Attribute] = MAX('Sheet1 (2)'[Attribute]) )
)
RETURN
IF(
HASONEFILTER('Sheet1 (2)'[Attribute]),
MAX('Sheet1 (2)'[Value]),
IF(
v1 <> 0,
total1,
v1_new+v2-v3
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Has your problem solved?
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous,
Please find below the solution using Calculated column.
Hope this is what you wanted to achieve.
Regards
Simran Tuli
@Anonymous
Not quite clear about the requirement and it will be easy to help you if you could share some sample data and the expected results. you can paste your data with the reply.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Data
Item | DATE | Value1 | Value2 | Value3 |
K60710 | 07/01/2020 0:00 | 4503 | 0 | 205 |
K60710 | 08/01/2020 0:00 | 0 | 1045 | 240 |
K60710 | 09/01/2020 0:00 | 0 | 1662 | 1080 |
K60710 | 10/01/2020 0:00 | 0 | 3044 | 1450 |
K60710 | 11/01/2020 0:00 | 0 | 1810 | 5816 |
K60710 | 12/01/2020 0:00 | 0 | 439 | 2800 |
K60710 | 01/01/2021 0:00 | 0 | 414 | 697 |
K60710 | 02/01/2021 0:00 | 0 | 1167 | 425 |
K60710 | 03/01/2021 0:00 | 0 | 1148 | 1560 |
K60710 | 04/01/2021 0:00 | 0 | 656 | 2050 |
K60710 | 05/01/2021 0:00 | 0 | 774 | 1513 |
K60710 | 06/01/2021 0:00 | 0 | 535 | 909 |
K60710 | 07/01/2021 0:00 | 0 | 496 | 996 |
K60710 | 08/01/2021 0:00 | 0 | 1158 | 841 |
K60710 | 09/01/2021 0:00 | 0 | 619 | 1688 |
K60710 | 10/01/2021 0:00 | 0 | 3822 | 1775 |
K60710 | 11/01/2021 0:00 | 0 | 1648 | 5396 |
K60710 | 12/01/2021 0:00 | 0 | 479 | 2392 |
K6PS64 | 07/01/2020 0:00 | 916 | 0 | 0 |
K6PS64 | 08/01/2020 0:00 | 0 | 0 | 0 |
K6PS64 | 09/01/2020 0:00 | 0 | 0 | 0 |
K6PS64 | 10/01/2020 0:00 | 0 | 0 | 0 |
K6PS64 | 11/01/2020 0:00 | 0 | 0 | 0 |
K6PS64 | 12/01/2020 0:00 | 0 | 0 | 0 |
K6PS64 | 01/01/2021 0:00 | 0 | 0 | 178 |
K6PS64 | 02/01/2021 0:00 | 0 | 196 | 204 |
K6PS64 | 03/01/2021 0:00 | 0 | 290 | 284 |
K6PS64 | 04/01/2021 0:00 | 0 | 348 | 366 |
K6PS64 | 05/01/2021 0:00 | 0 | 376 | 377 |
K6PS64 | 06/01/2021 0:00 | 0 | 209 | 226 |
K6PS64 | 07/01/2021 0:00 | 0 | 229 | 244 |
K6PS64 | 08/01/2021 0:00 | 0 | 320 | 275 |
K6PS64 | 09/01/2021 0:00 | 0 | 285 | 294 |
K6PS64 | 10/01/2021 0:00 | 0 | 401 | 343 |
K6PS64 | 11/01/2021 0:00 | 0 | 374 | 415 |
K6PS64 | 12/01/2021 0:00 | 0 | 210 | 320 |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
61 | |
59 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |