Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic time period measure

Hi, I would like to have a measure that adjusts to the time periods displayed in columns in a matrix, particularly when I am using a date with hierarchy and then the user expands to the next level. ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Anonymous , I created 1 such measure few days back. Now this was for Current Employee vs Last period employee

Last Period Employee =
var _min_date = minx(all('Date'),'Date'[Date])
var _Expression=if(ISFILTERED('Date'[Month Year]),maxx('Date',ENDOFMONTH(DATEADD('Date'[Date],-1,MONTH))),maxx('Date',DATEADD('Date'[Date],-1,YEAR)))
Return
CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=_Expression && Employee[Start Date]>=_min_date && (ISBLANK(Employee[End Date]) || Employee[End Date]>_Expression)),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

 

You can check more details at: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

 

I think is in scope and isfiltered can help to so.