Ongoing projects this week

[Anonymous]

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

[v-jayw-msft · Accepted solution]

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"))

Best Regards,

Jay

[Anonymous]

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]

@Anonymous , Create date table with weeks.

Refer to my hr blog on the same topic

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

for week related columns

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482