Dynamically update column values based on maximum slicer Date

[Anonymous]

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

1. If max slicer date is greater than Acquisition date, than "Opening balance" = sum (Acquisition Price + Acquisitions added – Depreciations Added – Write downs)
2. But if max slicer date is same as acquisition date, "Opending balance" = only (Acquistion price)
3. Hide rows if acquisition date is greate than the slicer date range

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

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

---

I have requirement to display data in Matrix and update "Opening Balance" calculated Column based on following rules

1. If max slicer date is greater than Acquisition date, than "Opening balance" = sum (Acquisition Price + Acquisitions added – Depreciations Added – Write downs)
2. But if max slicer date is same as acquisition date, "Opending balance" = only (Acquistion price)
3. 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

[Anonymous]

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 

1. Sums up, and shows only one value, i.e. 13,342.77 - 370.63 - 370.63 - 370.63 = 12,972.14; rather than increasing a fixed assett's value for each row...
2. & still shows the acquisitions/depreciations rows...

Click here for Here is the update file.pbix

thank you again

[v-piga-msft]

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