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" 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

 

 

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

3 Replies

  • 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