Creating Measures with a filter included

[Andy2410]

Hi

I am creating a dashboard for our Head of HR and she would like to see a breakdown of leavers who have less than 2 years service in increments of 18 months, 9 months, 6 months and 3 months but I am struggling to get these to work. 

So far I have a measure that will return the leavers in a rolling 12 months, now on the dashboard at the moment I can place this measure in a card and then apply a filter to that visual to limit it to 2 years service, but that won't work for the measures I need below that. 

Leavers rolling 12 months  =

CALCULATE(

    SUM('Reporting FactWorkForceMonthly'[NonFTELeavers]),

    DATESINPERIOD('Reporting DimDate'[Month Year],

    LASTDATE('Reporting DimDate'[Month Year]),-1,YEAR)

    )

As as worked example there were 53 leavers in the 12 month period that had less than 2 years service, so for the 18 months measure I want to calcuate the number of leavers out of the 53 that had less than 18 months service and so on down to 3 months.

Please let me know if you need further information.

Thank you

[Ashish_Mathur]

Hi,

I cannot understand your requireent.  Share some data to work with and show the expected result.

[Bmejia]

This is a different approach.  Create a new column on your employee table that calculates the months from today then just add a In between Sicer.

Months From Today = DATEDIFF(YourTable[Employment end date],TODAY(),MONTH)

The card can be dynamic based on slicer and using the months from today column or you can just create a static card which can also be edit to add separate breakouts by 3 or 6 months etc.. 

Count 3 Rolling Months = CALCULATE(Count(Query1[Name]),Query1[Months From Today]<=3