Dynamic Buckets

[JB_AT]

Hello

I have a measure that calulates the Employee Running Total
I also have a measure for Years of Service Running Total. Both work as expected.

What I would like to achieve is to create a dynamic bucket. The Bucket should contain the Amount of Employees that fall into a specific YoS bucket. 

Due to the complexity of the dataset behind above measures, I need to use them in any possible measure. 

I created a table with the Years of Services and have created this measure loosly based off other posts. But it doesn't return the result I want.

The end result should basically filter to the correct Years of Service when I choose different Years/Months, hense the use of Running Totals

Can anyone help?

VAR _MaxDate = MAX(DIM_Date[Date])
VAR Summary =
    SUMMARIZE (
        'Employee History',
       "Employee RT", [Employee RT],
        "Bucket", SWITCH (
            TRUE (),
            [Avg.Years of Service] >= 0 && [Avg.Years of Service] < 1, "0-1",
            [Avg.Years of Service] >= 1 && [Avg.Years of Service] < 3, "1-3",
            [Avg.Years of Service] >= 3 && [Avg.Years of Service] < 5, "3-5",
            [Avg.Years of Service] >= 5 && [Avg.Years of Service] < 10, "5-10",
            [Avg.Years of Service] >= 10 && [Avg.Years of Service] < 15, "10-15",
            [Avg.Years of Service] >= 15 && [Avg.Years of Service] < 20, "15-20",
            [Avg.Years of Service] >= 20 && [Avg.Years of Service] < 25, "20-25",
            [Avg.Years of Service] >= 25 , "25+"
        )
    )
RETURN
    SUMX ( Summary, IF ( [Bucket] = SELECTEDVALUE ( 'DIM Years Of Service'[Years Of Service Group]), 1, 0 ) )

[Ashish_Mathur]

Hi,

Your expected result is not clear.  Share data in a format that can be pasted in an MS Excel file and show the expected result.

[JB_AT]

Hi @Ashish_Mathur 

A Sample Data is here https://docs.google.com/spreadsheets/d/1h9pr1rixp-QJk6KplPci9BcZVS6Uigju/edit?usp=sharing&ouid=10513... 

I want to create Dynamic Years of Service Buckets and Count how many UniqueIds fall into those buckets. My Date Table has a relationship with the __Valid_From and __Valid_Until columns. When I change the date slicer to lets say 2022, then the Years of Service Groups change to implement this date. I created these two measures that work correctly and shows me the correct data when I use the date filter. 

I want to somehow use these measures to populate the buckets, or is there another way?

Thanking you in advance

Avg.Years of Service = VAR
    _MaxDate= MAX ( DIM_Date[Date] )
RETURN
CALCULATE (AVERAGEX(
    
         'Employee History',
         DATEDIFF ( 'Employee History'[__VALID_FROM], _MaxDate,DAY )/365.25
    ),
    FILTER ( ALL ( DIM_Date ), DIM_Date[Date] <= _MaxDate )
)

Employee RT = 

VAR MaxDate =
    MAX ( DIM_Date[Date] )
RETURN
    CALCULATE (
        [Employees],
        KEEPFILTERS (
            'Employee History'[__VALID_FROM] <= MaxDate
                && 'Employee History'[__VALID_UNTIL] >= MaxDate
        )),
        ALL ( DIM_Date ), USERELATIONSHIP('Employee History'[__VALID_FROM], DIM_Date[Date]))

[Ashish_Mathur]

Hi,

Someone else will help you with this.