I have this function which is working...
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-30"),
Duration = (Duration.Days(EndDate-StartDate)+1),
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
and now I want to get the StartDate, EndDate and Value variables from the table 'MSD Opportunity' from the fields [start_date],[end_date] and [value].
In DAX I'd just put 'MSD Opporunity'[start_date] and similarly for [end_date] and [value] but that isn't the right syntax for Power Query.
- I can't rename the table without a space - I suspect because I have quite a bit of DAX code written using the table name with a space so I don't want to undo all that if at all possible.
- I have tried Date.From(MSD Opportunity[start_date]) etc but Power Query doesn't like the space
- I have tried #"MSD Opportunity"[start_date] etc but I'm getting errors. I suspect because there's quite a lot happening on MSD Opportunity. Here's what #"MSD Opportunity" is...
let
_Today = DateTime.Date(DateTime.LocalNow()), // Obtain today's date
_MonthOfToday = Date.Month(_Today), // Obtain the month of today
_YearOfToday = Date.Year(_Today), // Obtain the year of today
_PositionInFY = 1, // Position in Financial year month parade This FY Oct = 1, This FY Nov = 2, and so on up to 24
_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,
_FirstDayOctThisFY = Date.From(DateTime.FromText(Number.ToText(_YearInFY) & "-10-01")), // First day of October in this financial year
Source = Cds.Entities("https://uccimierp.crm4.dynamics.com/main.aspx", [ReorderColumns=null, UseFormattedValue=null]),
entities = Source{[Group="entities"]}[Data],
opportunities = entities{[EntitySetName="opportunities"]}[Data],
#"Area of Interest Filter" = Table.SelectRows(opportunities, each List.Contains({"313e03ac-a7f5-ed11-8849-000d3aba3d76", "0a6d502d-27e8-ee11-904c-6045bdf456bf"}, [imi_areaofinterest])),
#"Remove NULL start dates" = Table.SelectRows(#"Area of Interest Filter", each not (Record.Field(_, "imi_imiplanneddeliverystartdate") = null or Record.Field(_, "imi_imiplanneddeliverystartdate") = "")),
#"Remove NULL end dates" = Table.SelectRows(#"Remove NULL start dates", each not (Record.Field(_, "imi_imiplanneddeliveryenddate") = null or Record.Field(_, "imi_imiplanneddeliveryenddate") = "")),
#"Only end dates in period" = Table.SelectRows(#"Remove NULL end dates", each Date.From([imi_imiplanneddeliveryenddate]) >= _FirstDayOctThisFY),
#"Removed Other Columns" = Table.SelectColumns(#"Only end dates in period",{"accountid", "contactid", "createdby", "createdon", "estimatedvalue", "imi_areaofinterest", "imi_imicustomlikelihood", "imi_imicustomlikelihood_display", "imi_imigfncode", "imi_imiplanneddeliveryenddate", "imi_imiplanneddeliverystartdate", "imi_opportunityid", "name", "opportunityid", "owninguser", "parentaccountid", "salesstage", "salesstage_display", "salesstagecode", "salesstagecode_display", "stageid", "statecode", "statecode_display", "statuscode", "statuscode_display"}),
#"Reformat date columns" = Table.TransformColumnTypes(#"Removed Other Columns", {{"imi_imiplanneddeliveryenddate", type date}, {"imi_imiplanneddeliverystartdate", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Reformat date columns", {"owninguser"}, #"MSD SystemUser", {"systemuserid"}, "MSD SystemUser", JoinKind.LeftOuter),
#"Expanded MSD SystemUser" = Table.ExpandTableColumn(#"Merged Queries", "MSD SystemUser", {"fullname"}, {"MSD SystemUser.fullname"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded MSD SystemUser", {"parentaccountid"}, #"MSD Account", {"accountid"}, "MSD Account", JoinKind.LeftOuter),
#"Expanded MSD Account" = Table.ExpandTableColumn(#"Merged Queries1", "MSD Account", {"name"}, {"MSD Account.name"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded MSD Account",{{"imi_imigfncode", "imi_imigfncode"}, {"imi_imiplanneddeliveryenddate", "end_date"}, {"imi_imiplanneddeliverystartdate", "start_date"}, {"MSD SystemUser.fullname", "owner"}, {"MSD Account.name", "company"}, {"estimatedvalue", "value"}, {"imi_opportunityid", "code"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "type", each "Opp"),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "likelihood", each if [imi_imicustomlikelihood_display] = "1%" or [imi_imicustomlikelihood_display] = "10%" or [imi_imicustomlikelihood_display] = "30%" then "(3) Low Likelihood < 50%"
else if [imi_imicustomlikelihood_display] = "50%" then "(2) Medium Likelihood >= 50%"
else if [imi_imicustomlikelihood_display] = "70%" or [imi_imicustomlikelihood_display] = "90%" or [imi_imicustomlikelihood_display] = "100%" then "(1) High Likelihood >= 70%"
else "Unknown"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "value_nov_thisFY2", each ValueThisMonth(2,11))
in
#"Added Custom2"
I've been trying to find answers myself for a good while now so any help greatly apprceciated. @OwenAuger - you're already somewhat familiar with what I'm trying to do.
