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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.