Forum Discussion

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , You can create a date table and join it with the planned start date and planned end date this and can use userelationship for inactive join say planned end date

 

first create date without timestamp like

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

 

Join to date table

 

measures

This Week planned = CALCULATE(countrows(Project) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

 

This Week end= CALCULATE(CALCULATE(countrows(Project) , USERELATIONSHIP ('Project'[Planned end date], 'Date'[Date])),  ) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

 

refer

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

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Amit,

 

Thanks for your reply.

 

I have already connected the Planned start date to the date table as primary connection and end start date as secondry one with the date table.

 

Date Table CreatedConnected to Date Table

 

In the below measure are you saying CountRows (Project) your talking about the ChangeRequest Table in my scenario right?

 

Also how do I create Week Rank Table?

 

Join to date table

 

measures

This Week planned = CALCULATE(countrows(Project) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

 

This Week end= CALCULATE(CALCULATE(countrows(Project) , USERELATIONSHIP ('Project'[Planned end date], 'Date'[Date])),  ) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))