Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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, ProjectNa...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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.