Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Rolling measure missing values

Hi everyone!

 

I searched through the forum and couldn't find the answer to my problem, so I hope you could help me.

 

It's a very simple table that I have so I hope the solution is simple as well.

 

Here is my Sample file .

 

What I'm trying to do is get the information on the number of employees who have left the company within the first three months of joining the company (later I'm dividing all terminated within 3 months of joining with total of hires during the same three months to get a %). The data is updated on a monthly basis.

 

I'm using a rolling measure with DATESINPERIOD, and though I get valid results with category "Company" which repeats every month some values are not showing for category "Department" (not all values repeat each month). The total, however, is correct, but the distribution by department is not (see also pivot table which shows the values I'm expecting for Rolling 3 months hire).

 

 

I appreciate your help!

 

Best,

 

O.

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You need employee left within three months of joining

     

    then it should be like

    measure  =

     

    calculate(count(Table[Employee]), filter(Table, not(isblank(Table[Temination Date])) && datediff([Hire Date],Table[Temination Date], month) <=3 ) )

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks a lot for your reply! However, my measure for number of leaving within three months of joining right now is:

     

    Measure = 
CALCULATE(CALCULATE([Sum_termination],DATESINPERIOD('Hire & Termination'[Entry_date],MAX('Hire & Termination'[Entry_date]),-3,MONTH)),DATESINPERIOD('Hire & Termination'[Report_date],MAX('Hire & Termination'[Report_date]),-3,MONTH))

     

    because I don't calculate number of leavers for one month, but for 3 rolling months. 

     

    However, when I do that, the result that I get for categories above is not correct even if total number is correct. Do you have any idea what I need to change to get the expected values?