topic Re: CALCULATIONS BASED ON 2 DATE COLUMNS in DAX Commands and Tips

CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Mon, 11 Jul 2022 14:04:21 GMT

Hello dear members,

I have a table with date_id, hire date of new employees, and the cost of the new employees:

DATE HIRE DATE COST NEWCOMERS TOTAL COST PER MONTH
1/1/2023 0
1/2/2023 1/2/2023 1100 1100
1/3/2023 1100
1/4/2023 1100
1/5/2023 1100
1/6/2023 1/6/2023 1000 2100 (explanation 1100+1000)
1/7/2023 2100
1/8/2023 1/8/2023 1200 3300 (explanation 2100+1200)
1/9/2023 3300
1/10/2023 3300
1/11/2023 3300

1/12/2023 3300

I need to calculate some bonuses based on the date and the hire date. For example during easter (date = 1/4/23) I want to calculate the easter bonus based on the hire date. So the Total Cost for date 1/4/23 will be 1100*1,5. That's not a problem, that's ok. The problem is during the summer and Christmat bonus. For example the summer bonus (1/8/23) must be calculated for the new employee who was hired prior 1/4/23 will be (1100*1,5) for the employee who was hired on 1/6/23 will be (1000+1000*0,5*(2/4)) and for the employee who was hired on 1/8/21 will be 1200.
The desired result for the column (Total Cost per Month) in row with date 1/8/23 should be (1100*1,5) +(1000+1000*0,5*(2/4) ) +1200.

I don't know how to achieve that with DAX .

Can you please please help me?

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

tamerj1 — Mon, 11 Jul 2022 14:14:37 GMT

Hi Dimitris_Kats
What are the 1.5, 0.5 and the 2/4? please expalin what does the calculation mean. Thank you.

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Mon, 11 Jul 2022 14:38:03 GMT

Hi tamerj1 and thank you very much for replying to me.

The 1,5 is the cost increase including the bonus. So if the cost is 1000 if I add the 50% for Easter bonus the total cost will be 1500 (100*1,5)

The 0,5 and the 2/4 is the calculation for summer bonus Total Cost *0,5*(2/4)
Is that helpful?

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

tamerj1 — Mon, 11 Jul 2022 18:39:20 GMT

Hi Dimitris_Kats
Please see the sample file with the proposed solution if conforms your requirement https://we.tl/t-ytefXoyLg7

TOTAL CURRENT COST = SUMX ( NewEmployees, CALCULATE ( SUM ( NewEmployees[TOTAL COST PER MONTH] ) * ( 1 + SUM ( Bonus[Percentage] ) ) ) )

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Mon, 11 Jul 2022 18:29:44 GMT

Thank you so much tamerj1 for your reply and your help.

The percentage table is really clever solutions. The only problem is that the bonus is given only during these 3 months. (April, August, December). The next month the cost is going back to the previous state (without the increase)

How can we achieve that??

Thank you very much again for your help

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

tamerj1 — Mon, 11 Jul 2022 18:40:01 GMT

Dimitris_Kats
The sample file and the solution are updated in the original reply.

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Tue, 12 Jul 2022 05:59:46 GMT

Thank you very much tamerj1 for the modification.
Unfortuntely the percentage table can't resolve the problem because the bonus is calcilated based on the hired date.
For example, if an employee is hired on January he will take 50% Easter bonus. If an employee is hired on February he will take 40% easter bonus and 80% Christams bonus...etc...
Is it possible somehow to add this in the calculation?

Thank you very very much

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Tue, 12 Jul 2022 06:32:28 GMT

tamerj1 I have created a table withh all the possible combinations:

Hire Date date_id Bonus
1/1/2023 20230401 0.5

1/2/2023 20230401 0.375

1/3/2023 20230401 0.25
1/4/2023 20230401 0.1

But i am not sure how to add the hire date to your measure.

Can you please help me out?

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

tamerj1 — Tue, 12 Jul 2022 06:42:44 GMT

Dimitris_Kats
Let me look into it probably this afternoon. Have a good day

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Tue, 12 Jul 2022 10:02:46 GMT

Of course, thank you very much,

You help is greatly appreciated!

Have a good day you too

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

tamerj1 — Tue, 12 Jul 2022 13:55:12 GMT

Hi Dimitris_Kats
Here is the updated solution. PBIX: https://we.tl/t-lKSlKZwakY Excel: https://we.tl/t-YptjzxpvQH

TOTAL CURRENT COST = VAR CurrentTotalCost = MAX ( NewEmployees[TOTAL COST PER MONTH] ) VAR CurrentDate = MAX ( NewEmployees[DATE] ) VAR EarlierTable = FILTER ( ALL ( NewEmployees ), NewEmployees[DATE] <= CurrentDate ) VAR CurrentBonus = SUMX ( EarlierTable, SUMX ( Bonus, IF ( Bonus[Hire Date] = NewEmployees[HIRE DATE], Bonus[Percentage] * NewEmployees[COST NEWCOMERS] ) ) ) RETURN CurrentBonus + CurrentTotalCost

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Tue, 12 Jul 2022 13:29:02 GMT

tamerj1 thank you so much for youe reply.

Can you please upload the new pbix? I tried to download it but it was the old one.

I haven't manage to make it work properly until now.
Thank you very much
I am really grateful for your help

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Tue, 12 Jul 2022 13:43:40 GMT

tamerj1 Also i am including the whole table with bonus combinations . So if we have an employee hired on january and one in June they will receive different Christmas bonus amount. They will take: cost-employee1*(1 +1) + cost-employee2*(1+0,875). I don't know if i have expained it correctly and if its possible to be done!!
I am really in need for this and i can't thank you enough for your effort and your time!!

HIRE DAT	DATE_ID	BONUS
1/1/2023	1/4/2023	0,5
1/2/2023	1/4/2023	0,375
1/3/2023	1/4/2023	0,25
1/4/2023	1/4/2023	0,125
1/5/2023	1/4/2023	0
1/6/2023	1/4/2023	0
1/7/2023	1/4/2023	0
1/8/2023	1/4/2023	0
1/9/2023	1/4/2023	0
1/10/2023	1/4/2023	0
1/11/2023	1/4/2023	0
1/12/2023	1/4/2023	0
1/1/2023	1/8/2023	0,5
1/2/2023	1/8/2023	0,5
1/3/2023	1/8/2023	0,5
1/4/2023	1/8/2023	0,5
1/5/2023	1/8/2023	0,5
1/6/2023	1/8/2023	0,5
1/7/2023	1/8/2023	0
1/8/2023	1/8/2023	0
1/9/2023	1/8/2023	0
1/10/2023	1/8/2023	0
1/11/2023	1/8/2023	0
1/12/2023	1/8/2023	0
1/1/2023	1/12/2023	1
1/2/2023	1/12/2023	1
1/3/2023	1/12/2023	1
1/4/2023	1/12/2023	1
1/5/2023	1/12/2023	1
1/6/2023	1/12/2023	0,875
1/7/2023	1/12/2023	0,75
1/8/2023	1/12/2023	0,625
1/9/2023	1/12/2023	0,5
1/10/2023	1/12/2023	0,375
1/11/2023	1/12/2023	0,25
1/12/2023	1/12/2023	0,125

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

tamerj1 — Tue, 12 Jul 2022 13:56:33 GMT

Dimitris_Kats
Files are now updated with the new data and links are updated with the new file. Please refer to the previous reply.

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Tue, 12 Jul 2022 18:05:45 GMT

Thank you very much tamerj1

I really can't describe how much you have helped me!!! I was wondering if you could explain me how this measure works. It's really incredible and complex.

I will check tomorrow morning the file and I will mark the thread as solved!

Thank you very much again

Have a great evening

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Wed, 13 Jul 2022 07:17:58 GMT

Hello tamerj1 I can't make it work properly as a measure. Is it possible to make it a calculated column?
At your file is working perfectly but in my model i have many to many relationship between the bonus and new employees table and I think this is causing me problems 😞
Please if you could look at it one last time, i would be really grateful.

Thank you very much again for everything!!

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

tamerj1 — Wed, 13 Jul 2022 09:18:04 GMT

Dimitris_Kats
Please let me know if you can join a teams or zoom meeting.

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Wed, 13 Jul 2022 10:07:16 GMT

Hello tamerj1
Thank you very much for your reply. I can't join a teams meeting right now but i could probably on afternoon.

Would you like to have a brief meeting this afternoon?
Also do you think its possible to make the measure calculated column?

I am sending you again the newcomers table with more complex data like my dataset

DATE	JOB TITLE	HIRE DATE	COST NEWCOMERS	TOTAL COST PER MONTH
1/1/2023	IT	1/1/2023	1000	1000
1/2/2023	IT			1000
1/3/2023	IT			1000
1/4/2023	IT			1000
1/5/2023	IT			1000
1/6/2023	IT			1000
1/7/2023	IT			1000
1/8/2023	IT	1/8/2023	1200	2200
1/9/2023	IT			2200
1/10/2023	IT			2200
1/11/2023	IT			2200
1/12/2023	IT			2200
1/1/2023	HR
1/2/2023	HR
1/3/2023	HR
1/4/2023	HR
1/5/2023	HR
1/6/2023	HR	1/6/2023	1300	1300
1/7/2023	HR			1300
1/8/2023	HR			1300
1/9/2023	HR			1300
1/10/2023	HR			1300
1/11/2023	HR			1300
1/12/2023	HR			1300

I really can't thank you enough for your help!!

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

tamerj1 — Wed, 13 Jul 2022 10:38:51 GMT

Yes sure. Let me know when you're free.

Re: CALCULATIONS BASED ON 2 DATE COLUMNS

Dimitris_Kats — Wed, 13 Jul 2022 11:00:02 GMT

Thank you very much tamerj1 !

I will send you later to arrange the time 🙂