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.
