Forum Discussion
Per Capita cal
Hi Neeko Tang,
Employee C is only 11 because in 2023 year 11 months are ongoing month. So employee C is having amount only for 11 (Nov) so the count for this year is 1. It will divide 1/11. If Employee C was present 2 months then it would divided by 2/11. Please let me know if there is still confusion. Thanks for your support.
Hi Junayet_Rahaman ,
We can create a table.
Table 2 = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2023,12,31)),"month",MONTH([Date]))
We can create measures.
Measure 2 =
var _total=IF(MAX('Table'[Year])<YEAR(TODAY()),12,MONTH(TODAY()))
var _table1=SUMMARIZE(ALL('Table'),'Table'[Employee name],'Table'[Month])
var _month=COUNTROWS(FILTER(_table1,'Table'[Employee name]=MAX('Table'[Employee name]) && [Month] in VALUES('Table 2'[month])))
return DIVIDE(_month,_total,0)Measure 3 =
var _a= CALCULATE(SUM('Table'[Amount]),ALL('Table'))
var _b=SUMX(VALUES('Table'[Employee name]),[Measure 2])
return _a * _b
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for your response. Not getting expected results. I have modified my table a little bit. Suppose Employee C worked in 2022 only for 2 months but again hen joined in November 2023. For 2022 will be 2/12 but in 2023 it will be 1/11.
Here is my updated data. I am using Year in all my charts. Will wait for your response. Thank you.
Payroll INC Period YearMonth Year Month Employee name Employee ID Type of emp Amount Paycode 2022-01-04 2022-04-01 202204 2022 4 A 25890 Local 29000 Y78IL 2022-01-04 2022-04-01 202204 2022 4 A 25890 Local 5900 O09PJ 2022-01-04 2022-04-01 202204 2022 4 A 25890 Local 5689 PO89U 2022-05-05 2022-05-01 202205 2022 5 A 25890 Local 29000 U8IOP 2022-05-05 2022-05-01 202205 2022 5 A 25890 Local 2589 P09NB 2022-08-06 2022-06-01 202206 2022 6 A 25890 Local 29000 TW76A 2022-08-06 2022-06-01 202206 2022 6 A 25890 Local 7825 EU76B 2022-08-06 2022-06-01 202206 2022 6 C 78965 Local 39878 JH87GH 2022-08-06 2022-06-01 202206 2022 6 C 78965 Local 8975 PO134LK 2022-08-07 2022-07-01 202207 2022 7 C 78965 Local 29678 RT09DP 2023-02-01 2023-01-01 202301 2023 1 B 57698 Contract 35000 YR09JT 2023-02-01 2023-01-01 202301 2023 1 B 57698 Contract 5000 YT64YU 2023-01-02 2023-02-01 202302 2023 2 B 57698 Contract 35000 AS8795 2023-01-02 2023-02-01 202302 2023 2 B 57698 Contract 6500 JGH670 2023-01-02 2023-02-01 202302 2023 2 B 57698 Contract 7815 KJ97GH 2023-02-03 2023-03-01 202303 2023 3 B 57698 Contract 35000 ST53GH 2023-02-03 2023-03-01 202303 2023 3 B 57698 Contract 983.3 BV87YU 2023-02-04 2023-04-01 202304 2023 4 B 57698 Contract 35000 NB84KJ 2023-02-05 2023-05-01 202305 2023 5 B 57698 Contract 35000 AQ23LP 2023-01-06 2023-06-01 202306 2023 6 B 57698 Contract 35000 ZX32FR 2023-01-06 2023-06-01 202306 2023 6 B 57698 Contract 7985.6 AS19FG 2023-02-07 2023-07-01 202307 2023 7 B 57698 Contract 35000 AB46JY 2023-02-11 2023-11-01 202311 2023 11 C 78965 Local 41562.5 AH80YU