DAX calculated table to create employee sales buckets based on their hire date

[iluvcoding_91]

Hey guys, I'm trying to create a DAX calculated table which sums up the sales for each employee based on their hire date, then places these sales into different buckets. For example, if Bob Dylan began working on 6/1/2021 his and his month 1 sales were $5,000, then this should go in column "D". If Hope Barron began working on 8/1/2021 and her first month sales were $6,000, then this amount should go into column "D". Then second month sales should go into column "E", etc. 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I created a sample pbix file in an expected way, but please check the below picture and the attached pbix file.

I tried to create a sample like below.

New Table = 
VAR summarytable =
    SUMMARIZECOLUMNS (
        Employee[Employee],
        'Calendar'[End of Month],
        "@SalesAmount", SUM ( Sales[Sales] )
    )
VAR firstsalesmonthtable =
    ADDCOLUMNS (
        summarytable,
        "@firstsalesmonth",
            MINX (
                FILTER ( summarytable, Employee[Employee] = EARLIER ( Employee[Employee] ) ),
                'Calendar'[End of Month]
            )
    )
VAR monthdifftable =
    FILTER (
        ADDCOLUMNS (
            firstsalesmonthtable,
            "@monthdiff", DATEDIFF ( [@firstsalesmonth], 'Calendar'[End of Month], MONTH ) + 1
        ),
        [@monthdiff] <= 3
    )
VAR descriptiontable =
    ADDCOLUMNS (
        monthdifftable,
        "@description", LOOKUPVALUE ( 'Description'[Description], 'Description'[index], [@monthdiff] )
    )
RETURN
    SUMMARIZE (
        descriptiontable,
        Employee[Employee],
        [@description],
        [@SalesAmount]
    )

[amitchandak]

@iluvcoding_91 , I have done this for customers here. Do same for employee

Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...