Forum Discussion
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):
Second Measure (it returns the start date of the project):
Fourth Measure (it tries to return the calendar):
But the calendar is returning the min and the max date of ALL projects, so it's clarely not working.
Any suggestions, ideas, recommendations?
2 Replies
Hi Anonymous
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.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?
