How To Link Two Different Accounts (Asset Vs Depreciation)

[I_NeedMorePower]

Hello power people!

I have in my AX 2012 a "MainAccount" table,

in this table there are accounts for assets and accounts for depreciation of the assets.

I want to create a table visual in Power BI that have a column for assets and it's total amount and a column for deprciation accounts and their total.

So it's like an asset account vs its depreciation account

What is the proper way to deal with this?

I tried to make a view for assets accounts and a view for depreciation accounts and then I created a depreciation account reference column in the asset view and made a many-to-many relationship between the two views with cross reference, but didn't work well when I change the date on the date slicer.

any advice would be appreciated!

Thanks in advance!

[amitchandak]

@I_NeedMorePower , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Without looking at data really difficult to tell.

You can use summarize or summarize and union

[I_NeedMorePower]

Yeah sure, here is an example I made in excel:

There are 3 tables: assets trans table (in blue), depreciation trans table (in purple) and linking table (in black)

the green tables represents the objective i am trying to reach.

I did manage to do the first green objective table by connecting the the 2 tables with the linking table

but my issue is when I have a date slicer filter and then choose a from day that has depreciation transactions but has no assets transactions.

the result I get is in the red table, even though there are deprectiation trans in the date slicer period choosen, it doesn't appear in the result table, becuase the linked asset id has no transactions at that time, so both of them disapears.

How can I get the data to appear as the 2nd green objective table?

sorry for the length.

[v-lili6-msft · Accepted solution]

hi  @I_NeedMorePower 

For your case, you could just try this way:

Step1:

Use a date table as a link table to create a relationship between assets trans table and depreciation trans table.

Step2:

Create  three measure as below:

Asset Amount = SUM('assets trans table'[Amount])+0

Deprectiation Amount = SUM('depreciation trans table'[Amount])+0

Net = [Asset Amount]-[Deprectiation Amount]

Result:

and  here is sample pbix file, please try it.

Regards,

Lin

[I_NeedMorePower]

Thank you for your detailed reply!

It really did the job!

but can I know if there is a way to hide the rows if both amounts of asset and depreciation were 0?

appreceiated!