Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamically update column values based on maximum slicer Date

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" calculat...
Show and Tell
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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