Forum Discussion

Andy2410's avatar
Andy2410
Frequent Visitor
2 years ago

Creating Measures with a filter included

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
Need Help

2 Replies

  • Bmejia's avatar
    Bmejia
    Icon for Super User rankSuper User
    2 years ago

    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

     





  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

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