Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Dynamic dCalendar based on variable data

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? 
Frequent Visitor

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? 

Community Support
Community Support

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.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors