Dynamic dCalendar based on variable data

[FortesLeo]

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? 

[FortesLeo]

Hello @v-jingzhang , thanks for the answer. Yeah, you're right, it keeps giving me the min/max dates across the whole table. Any ideas/suggestions about how can i solve my problem? 

[v-jingzhang]

Hi @FortesLeo 

The calendar table is a calculated table while a calculated table is added to the model. It is evaluated when it's created or when you refresh the dataset. It cannot be filtered or affected by any filters/measures/slicers in the report. In your formula for dCalendar, no [ProjectName] filter is passed to it so it gets min/max dates across the whole table. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.