Forum Discussion
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
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
3 Replies
Anonymous , Create date table with weeks.
Refer to my hr blog on the same topic
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/1187482Hey 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.
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
