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.
Hi everyone
I have the following task, which i am pulling my hairs for several hours
Attached is the PBIX File
I have requirement to display data in Matrix and update "Opening Balance" calculated Column based on following rules
I tried multiple solutions jumping between measures/Columns, but seems powerbi may not be the right tool to do some of these dynamic stuff?
will appreciate if you can help 🙂
Thank you
-Usman
Download Sample PBIX File here
Solved! Go to Solution.
Hi @Anonymous ,
I have requirement to display data in Matrix and update "Opening Balance" calculated Column based on following rules
- If max slicer date is greater than Acquisition date, than "Opening balance" = sum (Acquisition Price + Acquisitions added – Depreciations Added – Write downs)
- But if max slicer date is same as acquisition date, "Opending balance" = only (Acquistion price)
- Hide rows if acquisition date is greate than the slicer date range
From your pbix, it seems that you create the calculated column for Opening Balance, you'd better create the measure which is dynamic.
You could try the measure below based on your logic.
Measure = VAR a = CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR b = MAX ( 'Query1'[ACQUISITIONDATE] ) RETURN IF ( a = b, SUM ( 'Query1'[ACQUISITIONPRICE] ), IF ( a > b, SUM ( 'Query1'[ACQUISITIONPRICE] ) + SUM ( 'Query1'[Acquisition_Add] ) - SUM ( 'Query1'[Depreciation_Add] ) - SUM ( 'Query1'[Writedown_Add] ) ) )
Here is the output.
Best Regards,
Cherry
Hi @Anonymous ,
I have requirement to display data in Matrix and update "Opening Balance" calculated Column based on following rules
- If max slicer date is greater than Acquisition date, than "Opening balance" = sum (Acquisition Price + Acquisitions added – Depreciations Added – Write downs)
- But if max slicer date is same as acquisition date, "Opending balance" = only (Acquistion price)
- Hide rows if acquisition date is greate than the slicer date range
From your pbix, it seems that you create the calculated column for Opening Balance, you'd better create the measure which is dynamic.
You could try the measure below based on your logic.
Measure = VAR a = CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) ) VAR b = MAX ( 'Query1'[ACQUISITIONDATE] ) RETURN IF ( a = b, SUM ( 'Query1'[ACQUISITIONPRICE] ), IF ( a > b, SUM ( 'Query1'[ACQUISITIONPRICE] ) + SUM ( 'Query1'[Acquisition_Add] ) - SUM ( 'Query1'[Depreciation_Add] ) - SUM ( 'Query1'[Writedown_Add] ) ) )
Here is the output.
Best Regards,
Cherry
Bundle of thanks Cherry
A follow up question if ok 🙂
The total of opening balance measure is exceeding Assetts Acquisition price;
How can we display the Opening balance so that it
Click here for Here is the update file.pbix
thank you again
Hi @Anonymous ,
It seems that you have another requirement.
You'd better create another topic so that if your question has been answered people who may have the same question can get the solution directly.
Best Regards,
Cherry
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 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |