Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Having problems passing parameters into functions

I have this function - tested and working

 

let
    // accepts two parameters
    // PositionInFY - Position in Financial year month parade of the variable we're trying to find - This FY October = 1, This FY November = 2 and so on up to 24 for each month in the 2 years
    // PositionInYear - Position of month in calendar year with a leading zero if necessary
    // eg GetFirstDayOfMonth(19, 04) for next april
    GetFirstDayOfMonth = (_PositionInFY as number, _PositionInYear as number) =>
    let
        _Today = DateTime.Date(DateTime.LocalNow()),  // Obtain today's date
        _MonthOfToday = Date.Month(_Today),           // Obtain the month of today
        _YearOfToday = Date.Year(_Today),             // Obtain year of today
        // _PositionInYear = Text.From (_PositionInYear),
        _YearInFY = 
            if _MonthOfToday >= 10 then
                if _PositionInFY <= 3 then _YearOfToday
                else if _PositionInFY > 3 and _PositionInFY < 16 then _YearOfToday + 1
                else if _PositionInFY >= 16 then _YearOfToday + 2
                else null
            else
                if _PositionInFY <= 3 then _YearOfToday - 1
                else if _PositionInFY > 3 and _PositionInFY < 16 then _YearOfToday
                else if _PositionInFY >= 16 then _YearOfToday + 1
                else null,

        _GetFirstDayOfMonth = Date.FromText(Number.ToText(_YearInFY) & "-" & Number.ToText(_PositionInYear) & "-01")  // First day of Month
    in
        _GetFirstDayOfMonth
in
    GetFirstDayOfMonth

 

 

Then I want to create a second fundtion called ValueThisMonth that calls the GetFirstDayOfMonth function.

 

To start slowly, I started with this - putting all the parameters in and it works.  I expect to get 10,000 as the answer and I do.

 

let
        _PositionInFY = 2,
        _PositionInYear = 11,        
        FirstDayThisMonth = (GetFirstDayOfMonth(_PositionInFY, _PositionInYear)),
        LastDayThisMonth = Date.EndOfMonth(FirstDayThisMonth),
        DaysThisMonth = Duration.Days(LastDayThisMonth - FirstDayThisMonth),
        StartDate = Date.From("2023-11-01"),
        EndDate = Date.From("2023-11-30"),
        Duration = 30,
        Value = 10000,
        ValueThisMonth =
                
                        if StartDate < FirstDayThisMonth and EndDate < FirstDayThisMonth then 0
                        else if StartDate < FirstDayThisMonth and EndDate >= FirstDayThisMonth and EndDate <= LastDayThisMonth then
                            Number.Round((((Duration.Days(EndDate-FirstDayThisMonth)+1) / Duration) * Value), 0)
                        else if StartDate >= FirstDayThisMonth and EndDate <= LastDayThisMonth then
                            Number.Round((((Duration.Days(EndDate-StartDate)+1) / Duration) * Value), 0)
                        else if StartDate >= FirstDayThisMonth and StartDate <= LastDayThisMonth and EndDate > LastDayThisMonth then
                            Number.Round((((Duration.Days(LastDayThisMonth-StartDate)+1) / Duration) * Value), 0)
                        else if StartDate < FirstDayThisMonth and EndDate > LastDayThisMonth then Number.Round((DaysThisMonth / Duration) * Value, 0)
                        else if StartDate > LastDayThisMonth then 0
                        else 0
    in
        ValueThisMonth

 

 

But when I attempt to pass in the parameters it doesn't work...

 

let
    ValueThisMonth = (_PositionInFY as number, _PositionInYear as number) =>
    let
        FirstDayThisMonth = GetFirstDayOfMonth(_PositionInFY, _PositionInYear),
        LastDayThisMonth = Date.EndOfMonth(FirstDayThisMonth),
        DaysThisMonth = Duration.Days(LastDayThisMonth - FirstDayThisMonth),
        StartDate = Date.From(2023-11-01),
        EndDate = Date.From(2023-11-31),
        Duration = 30,
        Value = 10000,
        ValueThisMonth =
            if StartDate < FirstDayThisMonth and EndDate < FirstDayThisMonth then 0
            else if StartDate < FirstDayThisMonth and EndDate >= FirstDayThisMonth and EndDate <= LastDayThisMonth then
                Number.Round((((Duration.Days(EndDate-FirstDayThisMonth)+1) / Duration) * Value), 0)
            else if StartDate >= FirstDayThisMonth and EndDate <= LastDayThisMonth then
                Number.Round((((Duration.Days(EndDate-StartDate)+1) / Duration) * Value), 0)
            else if StartDate >= FirstDayThisMonth and StartDate <= LastDayThisMonth and EndDate > LastDayThisMonth then
                Number.Round((((Duration.Days(LastDayThisMonth-StartDate)+1) / Duration) * Value), 0)
            else if StartDate < FirstDayThisMonth and EndDate > LastDayThisMonth then Number.Round((DaysThisMonth / Duration) * Value, 0)
            else if StartDate > LastDayThisMonth then 0
            else 0
    in
        ValueThisMonth
in
    ValueThisMonth

 

 

Any help getting that to work would be greatly appreciated.

 

Then the next step will be to pass in parameters for the other variables which are held on a table as follows

 

StartDate = 'MSD Opportunity'[start_date]
EndDate = 'MSD Opportunity'[end_date]
Duration = 'MSD Opportunity'[Duration]
Value = 'MSD Opportunity'[value]

 

I have tried Chat GPT to help me but no joy.  Any help from humans greatly appreciated.

 

 

  • Hi Anonymous 

    There seem to be some small typos in your last function, specifically the lines with hard coded dates.

    This

     

    StartDate = Date.From(2023-11-01),
    EndDate = Date.From(2023-11-31),

     

     should change to this

     

    StartDate = Date.From("2023-11-01"),
    EndDate = Date.From("2023-11-30"),

     

    You should then be able to add the remaining function parameters.

     

    Does this help?

     

    Regards

3 Replies

  • Hi Anonymous 

    There seem to be some small typos in your last function, specifically the lines with hard coded dates.

    This

     

    StartDate = Date.From(2023-11-01),
    EndDate = Date.From(2023-11-31),

     

     should change to this

     

    StartDate = Date.From("2023-11-01"),
    EndDate = Date.From("2023-11-30"),

     

    You should then be able to add the remaining function parameters.

     

    Does this help?

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks for getting back to me Owen - I don't have time just now but I'll try this out on Monday.  Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks OwenAuger for pg that error - amazing what a new pair of eyes could see and you'd think at this stage I'd know there were only 30 days in November!!