Asset Depreciation Forecast - custom function

[Tibinho10]

Hi folks,

I am relatively new to Power Query and trying to simulate straightline depreciation for my assets (50k assets) in Excel. Since the dataset is big and assets are added every month I am unsure how to enable user droping forecasted depreciation value per month to their power pivot or how to build visualisation (line chart for future months). What would be a clever way around this please:

custom (universal) function
variable
measure
custom column/query step
new blank query

come to mind. Monthly depreciation is calculated as acquisition value ('**bleep**.acq.value' field) divided by useful life ('Life' field contains useful life in 'Years/Months' format). Depreciation runs only up to the depreciation end date which should be calculated as Start date ('Depre Start Date') plus useful life ('Life' field= useful life in 'Years/Months' format). 'Asset' number is not a unique number, it is unique in combination with 'Sub-number'. I believe below two tables should suffice to build a dynamic monthly depreciation forecast per asset, function or field which may be then used in power pivot and visualisations. Date table contains dates from 1/1/2020 till 31/12/2040.

Thank you.

[Tibinho10]

Date	Year	Month	Month Name	Quarter	Start of Month	Fiscal Month	Fiscal Quarter	Fiscal Year
7/1/2020	2020	7	Jul	3	7/1/2020	4	2	2021
7/2/2020	2020	7	Jul	3	7/1/2020	4	2	2021
7/3/2020	2020	7	Jul	3	7/1/2020	4	2	2021
7/4/2020	2020	7	Jul	3	7/1/2020	4	2	2021
7/5/2020	2020	7	Jul	3	7/1/2020	4	2	2021
7/6/2020	2020	7	Jul	3	7/1/2020	4	2	2021
7/7/2020	2020	7	Jul	3	7/1/2020	4	2	2021
7/8/2020	2020	7	Jul	3	7/1/2020	4	2	2021
7/9/2020	2020	7	Jul	3	7/1/2020	4	2	2021

[Tibinho10]

[Tibinho10]

Asset	Sub-number	Depreciation Calculation Start Date	Life	Cumul.acq.value	Currency
32100022222	0	1/1/2013	020/000	47,189.18	EUR
32100012345	0	1/1/2013	018/000	3,731.77	EUR
32100012345	1	1/1/2013	020/005	6,717.73	EUR
32100012345	2	1/1/2013	020/000	147,671.87	EUR
32100044444	0	1/1/2012	005/001	13,317.33	EUR
32100044444	1	1/1/2012	005/001	9,761.00	EUR
32114560061	0	1/1/2012	000/001	188.43	EUR
32123456007	0	1/1/2012	000/001	83.73	EUR
32123456008	0	1/1/2012	005/001	3,736.84	EUR

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...