Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Incorrect yearly "Opening Balance" values for "Fixed Assets"

hi every one

 

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

 

 

The above is working, but issue is Total of opening balance measure is exceeding Assetts Acquisition price; (summed repetitively on each row)

How can I 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...

 

Do i have to use a different format? 🙂 

 

Please click here for the PBIX File

 

FA3.PNG

 

will appreciate if you can help Smiley Happy

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi, @Anonymous 

There is a wrong logic for your Total of opening balance measure.

First, there are four rows of data for FIXEDASSETNUMBER"11000", so there four rows of data for ACQUISITIONPRICE "13342.77"

Therefore, SUM ( 'Query1'[ACQUISITIONPRICE] ) will be calculated repetitively for FIXEDASSETNUMBER which has multiple rows of data.

Second, for the relationship between date table and Query1, Cardinality should be Many to One, and Cross filter direction should be "Single"

12.JPG

 

Then you could try this data model:

Step1:

Create a FIXEDASSETNUMBER and ACQUISITIONPRICE fact table then create the relationship with Query1 by FIXEDASSETNUMBER 

13.JPG

Note: Be careful with the relationship between each table.

Step2:

Then adjust your formula

Measure 3 = 

VAR a =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR b =
    //MAX ( 'Query1'[ACQUISITIONDATE] )
    MAX ( 'Query1'[TRANSDATE] )
RETURN
    IF (
        a = b,
        SUM ( 'Fact'[ACQUISITIONPRICE] ),
        IF (
            a > b,
            SUM ( 'Fact'[ACQUISITIONPRICE]) + SUM ( 'Query1'[Acquisition_Add] )
                - SUM ( 'Query1'[Depreciation_Add] )
                - SUM ( 'Query1'[Writedown_Add] )
        )
    )

Step3:

Drag FIXEDASSETNUMBER and ACQUISITIONPRICE from fact table instead of Query1 

14.JPG

Result:

15.JPG

here is new pbix file, please try it.

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi, @Anonymous 

There is a wrong logic for your Total of opening balance measure.

First, there are four rows of data for FIXEDASSETNUMBER"11000", so there four rows of data for ACQUISITIONPRICE "13342.77"

Therefore, SUM ( 'Query1'[ACQUISITIONPRICE] ) will be calculated repetitively for FIXEDASSETNUMBER which has multiple rows of data.

Second, for the relationship between date table and Query1, Cardinality should be Many to One, and Cross filter direction should be "Single"

12.JPG

 

Then you could try this data model:

Step1:

Create a FIXEDASSETNUMBER and ACQUISITIONPRICE fact table then create the relationship with Query1 by FIXEDASSETNUMBER 

13.JPG

Note: Be careful with the relationship between each table.

Step2:

Then adjust your formula

Measure 3 = 

VAR a =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR b =
    //MAX ( 'Query1'[ACQUISITIONDATE] )
    MAX ( 'Query1'[TRANSDATE] )
RETURN
    IF (
        a = b,
        SUM ( 'Fact'[ACQUISITIONPRICE] ),
        IF (
            a > b,
            SUM ( 'Fact'[ACQUISITIONPRICE]) + SUM ( 'Query1'[Acquisition_Add] )
                - SUM ( 'Query1'[Depreciation_Add] )
                - SUM ( 'Query1'[Writedown_Add] )
        )
    )

Step3:

Drag FIXEDASSETNUMBER and ACQUISITIONPRICE from fact table instead of Query1 

14.JPG

Result:

15.JPG

here is new pbix file, please try it.

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you a bundle Lin 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.