Parameters based on measures in custom table?

[CEllinger]

I am trying to use an M function that I found to build an Amortization Calculator. The code I am starting with is:

let MortgageAmortization = (P,i,n)=>

let

Payment = P*((i/12)*Number.Power(1+(i/12),n))/(Number.Power(1+(i/12),n)-1),

Payments =
    Table.FromList(

        List.Generate(
()=>[Counter=0],
each [Counter]<n,
each [Counter=[Counter]+1],
each P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Counter]+1))/(Number.Power(1+(i/12),n)-1))

,Splitter.SplitByNothing(), {“Balance”}, null, ExtraValues.Error),

MonthlyInterest = Table.AddColumn(Payments,”Monthly Interest”,each (i/12)*[Balance]),

MonthlyPrincipal = Table.AddColumn(MonthlyInterest,”Monthly Principal”,each Payment-[Monthly Interest]),

MonthlyPayment = Table.AddColumn(MonthlyPrincipal,”Monthly Payment”,each Payment)       
in
MonthlyPayment
in
MortgageAmortization

I end up with a screen that looks like this:

Is it possible to tie these parameters directly to measures that I am using in my tables?

Is there a work around? I am semi-versed in DAX but this is my first foray in to M.

[CEllinger]

Thanks for the quick responses @Mariusz and @Geradav.

It is looking more and more like the business ask I am working on requires some type of recursive calculation. In my brief research, it seems like DAX doesnt like to do recursion so I am left only with M.

If I cant call DAX directly, here is the only other workaround I can think of. Maybe you can help me out. If not, I can post a separate question:

I have normal date and sales tables.

I have a disconnected Date Table that I made in M:

let
    Dates = List.Zip({List.Dates(#date(2019,1,1),365,#duration(1,0,0,0))}),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
    #"Renamed Columns"

And I have another table called "Dynamic Dates" that directly references the above table ("Dates"):

let
    Source =    List.Dates(List.Min(Dates[Date]),
                Duration.Days(List.Max(Dates[Date])-List.Min(Dates[Date])),
                #duration(1,0,0,0)),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
    #"Renamed Columns"

I have two measures built in to my report.

Start Date = MIN(DynamicDates[Date])

End Date = MAX(DynamicDates[Date])

Using those measures, I can use a Date Slicer Visual on my report to dynamically change my calculations. (see photos below)

I want to re-add the Extra to the remaining dates. Is it possible, using M, to Generate a table that is only the Dates from Max(Dynamic Dates) through the end of the year? Then I was thinking that a Calculated Column with Generate List might provide me with my solution...

Thoughts?

[Geradav]

Hi @CEllinger 

If I am getting you right, you'd like to M Power Query parameters and custom function elements in your DAX?

Well, to my knowledge this is not possible.

The function you have in Power Query can only be used in Power Query.

If you want to have an input form that users can fill and obtain some result, an updated visual or an updated table based on user input, you could use the Power Apps custom visual, design a form based on your dataset, and integrate it into your report.

Then you would use Power Automate to post a request and receive the corresponding output.

But maybe I got you wrong. Not what's your end goal.

Let us know and maybe give us more details.

Best

David

[Mariusz]

Hi @CEllinger 

You can not pass any DAX back to Power Query as it stands.

What is the reason for your requirement?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn