calculating time period based on Hire

[yhv]

I have 2 measures Sales and Revenue
I have to create a time period 0-3Months,3-6Months,6-9Months,9-12Months etc as calculated column based on EMployees Hire Date.
This logic for the time period should be Hire Date +3Months,Hire Date +6Months,Hire Date +9Months,Hire Date +12Months etc
to track the employee performance.

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

I followed the below steps.

- create an axis table that contains every_three_month_axis column based on the maximun number from the fact table

- create cumulate values for trend comparison that all of them start from zero.

Axis_calculated_table = 
VAR _monthcountperemployee =
    GROUPBY (
        ADDCOLUMNS (
            SUMMARIZE (
                'work_hour_fact',
                'employee_dim'[employee],
                'calendar_dim'[year_month_sort]
            ),
            "@monthcount",
                COUNTROWS (
                    FILTER (
                        SUMMARIZE (
                            'work_hour_fact',
                            'employee_dim'[employee],
                            'calendar_dim'[year_month_sort]
                        ),
                        'employee_dim'[employee] = EARLIER ( 'employee_dim'[employee] )
                    )
                )
        ),
        'employee_dim'[employee],
        "@maxmonthcount", MAXX ( CURRENTGROUP (), [@monthcount] )
    )
VAR _maxmonthcount =
    ROUNDUP ( MAXX ( _monthcountperemployee, [@maxmonthcount] ) / 3, 0 ) * 3
VAR _axis =
    GENERATESERIES ( 1, _maxmonthcount, 1 )
RETURN
    ADDCOLUMNS (
        SELECTCOLUMNS ( _axis, "@monthindex", [Value] ),
        "@every_three_month", ROUNDDOWN ( DIVIDE ( [@monthindex] - .1, 3 ), 0 )
    )

cumulate working hour by axis: = 
VAR _t =
    SUMMARIZE (
        work_hour_fact,
        employee_dim[employee],
        work_hour_fact[work_hour],
        calendar_dim[year_month_sort]
    )
VAR _monthnumber =
    ADDCOLUMNS (
        _t,
        "@monthnumber", RANK ( SKIP, _t, ORDERBY ( calendar_dim[year_month_sort], ASC ) )
    )
VAR _matchtoaxis =
    ADDCOLUMNS (
        FILTER (
            ALL ( Axis_calculated_table[@monthindex] ),
            Axis_calculated_table[@monthindex] <= MAX ( Axis_calculated_table[@monthindex] )
        ),
        "@work_hour",
            SUMX (
                FILTER ( _monthnumber, [@monthnumber] = Axis_calculated_table[@monthindex] ),
                work_hour_fact[work_hour]
            )
    )
VAR _condition =
    SUMX (
        ADDCOLUMNS (
            VALUES ( Axis_calculated_table[@monthindex] ),
            "@work_hour",
                SUMX (
                    FILTER ( _monthnumber, [@monthnumber] = Axis_calculated_table[@monthindex] ),
                    work_hour_fact[work_hour]
                )
        ),
        [@work_hour]
    )
        <> BLANK ()
RETURN
    IF ( _condition, SUMX ( _matchtoaxis, [@work_hour] ) )

[yhv]

Hi Jihwan

Thank You for your reply. What is the "work_hour_fact" Column here. I need the grouping values as 0-3M,3-6M,6-9M.....and so on.

[kriscoupe]

Hey @yhv ,

Assuming you want to analyse Sales and Revenue by employees length of service you could create a calculated column on the Employees table using the DAX statement below

Length of Service = 
VAR _todaysDate = TODAY()
RETURN
SWITCH(
    TRUE(),
    _todaysDate >= EDATE(Workday[Hire Date], 12), "Over 12 Months",
    _todaysDate >= EDATE(Workday[Hire Date], 9), "9-12 Months",
    _todaysDate >= EDATE(Workday[Hire Date], 6), "6-9 Months",
    _todaysDate >= EDATE(Workday[Hire Date], 3), "3-6 Months",
    _todaysDate < EDATE(Workday[Hire Date], 3), "Less than 3 Months"
)

You can then use this new calculated column in a table or graph to aggregate Sales and Revenues into these buckets. Be sure to refresh your dataset regularly so that the buckets get updated as time goes by.

Hope it helps,

Kris

[yhv]

Hi Kris

Thanks for the reply. I tried using the logic that mentioned here. I am only seeing over 12 months value, I am not able to see other values .

[kriscoupe · Accepted solution]

Hey @yhv ,

Apologies wasn't in a position to be able to test. Had another look and altered my original post to use the EDATE function which returns a date X number of months in the future from the date specified. Picture below illustrates in action. Learned something myself.. I've altered my original post.

Let me know if that fixes it for you! As mentioned by @Jihwan_Kim above a picture of your model will help so we know the table structures as well if this does not work.

[yhv]

Hi
Can we add ranking for length of service column. 

I have to calculte Cumulative and average based on this ranking.