Forum Discussion
DAX Consultation
Hello People, I hope it is going well.
I'm testing some functions for a personal project and I'm having problems with a logic.
I have a Sales table that contains employee code, date, values, and an Employee table that contains employee code, country, duration (how many months it has been in the company).
How can I calculate sales per employee by prorating by duration? That is, I need to calculate Sales per employee for the first 3 months, from months 4 to 6, 7-9, 10-12, >12.
If the duration is <= 3 then divide it by the duration.
If it is <= 6 then divide by 3 both periods, if the duration is <= 5 divide the sales of the period 4-6 by 2 and the sales 0-3 by 3, if it is <=4 divide the period 4-6 by 1 and the period 0-3 by 3.
If it is <= 9 then divide the periods by 3, if the duration is <= 8 divide the sales of period 7-9 by 2 and the rest of the periods by 3 each period, if it is <=7 divide period 7-9 by 1 and the rest of the periods by 3 each.
If it is <= 12 then divide by 3 each of the periods, if the duration is <= 11 divide the sales of period 10-12 by 2 and the rest of the periods by 3, if it is < = 10 divide the period 10-12 by 1 and the rest of the periods by 3
If it is >12 then divide by the duration.
I hope it is understood, if more details are needed, tell me.
Thank you!!!
5 Replies
Hola @Syndicate_Admin
Here is the link to the excel mockup: https://www.transfernow.net/dl/20240802i4CwOQFc
The idea would be to have a table of employees with sales for the period, something like this, in this case as it has a duration of 11 it would be an avg of 4-6 (as a duration greater than 6 then that period divided 3, the same with the period 7-9 and in the period 10-12 there add the values but dividing by 2 since it does not have 12 duration.
Number Employee Code Start Date Duration 0-3
months4-6
months7-9
months10-12
monthsJuan Antonio Andujar 1 20/06/2023 11 0 15812,5 7000 5625 Any other questions you can ask me. I hope you understand.
Thank you very much in advance!
Hello @Syndicate_Admin , any luck with this query? Thank you!!
Hi Syndicate_Admin , Daniel29195 thank you for your prompt reply!
Based on the description, please use the following measure to check the result:Measure = VAR UserDuration = MAX('Employee'[Duration]) VAR Q1 = CALCULATE(SUM('Sales'[SalesValue]), FILTER('Sales', MONTH('Sales'[Date]) >= 1 && MONTH('Sales'[Date]) <= 3)) VAR Q2 = CALCULATE(SUM('Sales'[SalesValue]), FILTER('Sales', MONTH('Sales'[Date]) >= 4 && MONTH('Sales'[Date]) <= 6)) VAR Q3 = CALCULATE(SUM('Sales'[SalesValue]), FILTER('Sales', MONTH('Sales'[Date]) >= 7 && MONTH('Sales'[Date]) <= 9)) VAR Q4 = CALCULATE(SUM('Sales'[SalesValue]), FILTER('Sales', MONTH('Sales'[Date]) >= 10 && MONTH('Sales'[Date]) <= 12)) VAR TotalValue = SUM('Sales'[SalesValue]) RETURN SWITCH( TRUE(), UserDuration <= 3, Q1 / UserDuration, UserDuration <= 6, (Q1 / 3) + (Q2 / (UserDuration - 3)), UserDuration <= 9, (Q1 / 3) + (Q2 / 3) + (Q3 / (UserDuration - 6)), UserDuration <= 12, (Q1 / 3) + (Q2 / 3) + (Q3 / 3) + (Q4 / (UserDuration - 9)), TotalValue / UserDuration )Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Syndicate_Admin , it's not exactly what I was looking for but your help gave me a good hand! What I ended up doing, since I needed a measure for each period, that is, a measure for 0-3 months, another for 4-6 and so, I created a measure to calculate the sales for the previous month, one for today -1 -2 and so on until I have the sales from 12 months ago and back. And then to another extent I gathered the sales by period and divided it taking into account the User Duration and an if.
Thank you very much for the help!
