Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Ongoing projects this week

Dear community,

 

I am stuck with date filtering in PowerBI Desktop. So here is the issue:
I have a table loaded from SalesForce that gives me a list of projects with StartDate, EndDate, ProjectName, ExpectedLoad etc.

What I want is to be able to create a report where I can select the current week and see a list of all projects that are active that week, meaning the current week falls in between StartDate and EndDate.

 

I am really stuck on this and appreciate if someone has a good idea on how to solve this.

 

Thanks,

MarNeu

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

One thing I want to confirm is what you mean by "this week". Let's say today is 4/14/2021 and the end date of the project is 4/13/2021, if this project is considered as an active project in this week then you could check the following steps.

You will need a calendar table and create a yearweek column as slicer.

yearweek = YEAR('calendar'[date])&WEEKNUM('calendar'[date],2)
Measure = IF(ISBLANK(SELECTEDVALUE('Table'[end]))&&YEAR(SELECTEDVALUE('Table'[start]))&WEEKNUM(SELECTEDVALUE('Table'[start]),2)=SELECTEDVALUE('calendar'[yearweek]),"active",IF(YEAR(SELECTEDVALUE('Table'[end]))&WEEKNUM(SELECTEDVALUE('Table'[end]),2)=SELECTEDVALUE('calendar'[yearweek]),"active","inactive"))

4.PNG

5.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

One thing I want to confirm is what you mean by "this week". Let's say today is 4/14/2021 and the end date of the project is 4/13/2021, if this project is considered as an active project in this week then you could check the following steps.

You will need a calendar table and create a yearweek column as slicer.

yearweek = YEAR('calendar'[date])&WEEKNUM('calendar'[date],2)
Measure = IF(ISBLANK(SELECTEDVALUE('Table'[end]))&&YEAR(SELECTEDVALUE('Table'[start]))&WEEKNUM(SELECTEDVALUE('Table'[start]),2)=SELECTEDVALUE('calendar'[yearweek]),"active",IF(YEAR(SELECTEDVALUE('Table'[end]))&WEEKNUM(SELECTEDVALUE('Table'[end]),2)=SELECTEDVALUE('calendar'[yearweek]),"active","inactive"))

4.PNG

5.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

Hey Amitchandak,

 

thank you very much for the help. I created the date table with the right relationships (start date relationship is active, end date is inactive).

I am still encountering the following problems:

1) When I create a filter on a certain week, it filters down my projects to the ones only starting in this specific week. But I wanted the graph to show all projects that are running in that specific week. So they could have started weeks before already and end in a couple of months. But they don't show up in the graph.

2) The calculation doesn't seem to work for the workload. Basically I have a column that states the expected workload per week as decimal number per project. I want to have a calculation that allows me to show the workload per assigned project manager and per week, for load balancing

 

Hope you can help me on this.

amitchandak
Super User
Super User

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.