Power Query to Find Project Names which do not have update in current month

Hi dbollini,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to retrieve project names from the project master table, but only for those projects that did not have an entry in the monthly uodates table for a selected month start date. 

Here is the code example in DAX and PowerQuery.

* DAX approach : 

ProjectsWithoutUpdate =
VAR SelectedMonth = SELECTEDVALUE(MonthlyUpdates[monthstartdate]) -- Get the selected month

RETURN
FILTER(
ProjectMaster,
ProjectMaster[ProjectStatus] IN {"On-Track", "Delayed", "At-Risk"} &&
NOT ProjectMaster[ID] IN
FILTER(
MonthlyUpdates,
MonthlyUpdates[monthstartdate] = SelectedMonth
)
)

* Power Query (M) Approach : 

let
ProjectMaster = Source{[Name="ProjectMaster"]}[Content],
MonthlyUpdates = Source{[Name="MonthlyUpdates"]}[Content],

// Filter Monthly Updates for selected month
SelectedMonth = Date.From(#"Your Filter Date Here"),
FilteredMonthlyUpdates = Table.SelectRows(MonthlyUpdates, each [monthstartdate] = SelectedMonth),

// Remove projects that exist in MonthlyUpdates
MergedTables = Table.NestedJoin(ProjectMaster, "ID", FilteredMonthlyUpdates, "projectid", "Updates", JoinKind.LeftAnti),

// Keep only relevant project statuses
FilteredProjects = Table.SelectRows(MergedTables, each List.Contains({"On-Track", "Delayed", "At-Risk"}, [ProjectStatus]))

in
FilteredProjects

I would also take a moment to thank ArwaAldoud, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.