topic Re: DAX calculated table to create employee sales buckets based on their hire date in DAX Commands and Tips

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

iluvcoding_91 — Mon, 11 Apr 2022 20:46:44 GMT

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.

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

amitchandak — Tue, 12 Apr 2022 02:33:00 GMT

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-Analysis/ba-p/1393410

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

Jihwan_Kim — Tue, 12 Apr 2022 02:46:03 GMT

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] )