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