Hello guys.
So, i'm working with Project Web App (PWA), and it provides information from multiple projects, like the [ProjectID], [ProjectName], [ProjectStartDate] and [ProjectFinishDate].
One information that i need is the actual week of each project, and it's not realated with the project's year.
For example: i have a project "a", that have a [ProjectStartDate] = 04/05/2022 and a [ProjectFinishDate] = 12/12/2023, from that the first week goes from 01/04/2022 till 10/05/2022, and the second from 11/05/2022 till 17/05/2022. At the same time, i have a project "b", that have a [ProjectStartDate] =10/04/2023 and a [ProjectFinishDate] = 16/04/2023, from that the first week goes from 10/04/2023 till 16/04/2023, and subsequently. And many more projects: a "c", "d", "e"...
Is there a way to solve this problem? I ask this bcs the way that i build the report, the person that is using it should choose from a data segmentation wich project should be displayed. I already tried to use Calendar() to build a table with variables, but it doesn't seems to work with the measures of start and end date, like the following:
First Measure (it returns the name of the selected project):
ProjectNameMeasure = MAX(Projects[ProjectName])
Second Measure (it returns the start date of the project):
ProjectStartDateMeasure = CALCULATE(MIN(Projects[ProjectStartDate].[Date]),FILTER(Projects,Projects[ProjectNameMeasure]=[ProjectName]))
Third Measure (it returns the finish date of the project):
ProjectFinishDateMeasure = CALCULATE(MAX(Projects[ProjectFinishDate].[Date]),FILTER(Projects,Projects[ProjectNameMeasure]=[ProjectName]))
Fourth Measure (it tries to return the calendar):
dCalendar=CALCULATETABLE(CALENDAR ([ProjectStartDateMeasure],[ProjectFinishDateMeasure ]),ALLEXCEPT(Projects,Projects[ProjectName]))
But the calendar is returning the min and the max date of ALL projects, so it's clarely not working.
Any suggestions, ideas, recommendations?
