calculate the amount per candidate only for first 8 months

[andra2]

Hello, 

I want to calculate for each candidate which is the amount only for the first 8 months of contract, no matter if he has only one or more placements. For instance, I have this sample of data for which I will expect to receive the below desired result:

Candidate ID	Placement	Amount	Start Date	End Date	Comments	Desired Result
x	1	10000	1/1/2020	10/31/2020	no new placement, more than 8 months	8000 - for this case, we need to calculate only 8 months from the amount
y	1	400	1/1/2020	3/31/2020	current placement less than 8 months	400 - placement 1, for this case we take into consideration the entire amount as 8 months did not passed and is the first placement
y	2	12000	7/1/2020	12/31/2020	second placement, reaches 8 months with the previous placement	800 - 2nd placement, we need to take into consideration only 4 months from this placement  as we had in the first placement 4 months
z	1	400	1/1/2020	3/31/2020	current placement less than 8 months	400 - placement 1, for this case we take into consideration the entire amount as 8 months did not passed and is the first placement
z	2	500	7/1/2020	9/30/2020	second placement less than 8 months	500 - 2nd placement, we need to take into consideration the entire amount as in the placement we have only 3 months of contract and the overall 8 months are not yet fulfilled
z	3	10000	1/1/2022	5/31/2022	thrid placement, fulfills the 8 months	400 - 3rd placement, we need to calculate the amount only for the remaining months: 2 months =  8 months total months - 3 from the first placement - 3 from the second placement

Candidate x	8000
Candidate y	1200
Candidate z	13000

Can you, please, help me with the calculations I need to deploy for it?

Many thanks!

[Barthel]

Hey,

Here is a way to process the result in a calculated column. First calculate the difference in months between the start and end date:

Duration (Month) = 
DATEDIFF ( 'Table'[Start Date], 'Table'[End Date], MONTH ) + 1

You can then calculate the desired result based on this:

Result =
VAR StartMonth =
    CALCULATE (
        SUM ( 'Table'[Duration (Month)] ),
        ALLEXCEPT ( 'Table', 'Table'[Candidate ID] ),
        'Table'[Placement] < EARLIER ( 'Table'[Placement] )
    )
VAR Months =
    IF (
        ISBLANK ( StartMonth ),
        IF ( 'Table'[Duration (Month)] > 8, 8, 'Table'[Duration (Month)] ),
        IF ( 8 - StartMonth < 0, 0, 8 - StartMonth )
    )
VAR Result =
    DIVIDE ( 'Table'[Amount] * Months, 'Table'[Duration (Month)] )
RETURN
    Result

Result:

Use the 'Result' column to calculate totals per candidate.

[andra2]

thank you very much. But if I have same placement at candidate level? I need also for this scenario 😞

[Barthel · Accepted solution]

Hey,

Each candidate should have a unique placement for the cumulative values ​​to work correctly. With duplicate values ​​you should create a new unique placement. This can be done by means of the RANKX function. Create an extra calculated column:

Rank = 
VAR _Rank =
    RANKX (
        CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Candidate ID] ) ),
        'Table'[Placement],
        ,
        ASC,
        SKIP
    )
VAR _Tie =
    RANKX (
        CALCULATETABLE (
            'Table',
            ALLEXCEPT ( 'Table', 'Table'[Candidate ID], 'Table'[Placement] )
        ),
        'Table'[Start Date],
        ,
        ASC
    ) - 1
VAR _Result = _Rank + _Tie
RETURN
    _Result

If the placements are the same, a unique rank is calculated based on the start date. If they are also the same, you will have to determine a rank in a different way. You could create an index column in Power Query per candidate. Here's a good article on how you could do that: https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/ 

In the 'Result' column, don't refer to 'Placement', but to the 'Rank':

Result = 
VAR StartMonth =
    CALCULATE (
        SUM ( 'Table'[Duration (Month)] ),
        ALLEXCEPT ( 'Table', 'Table'[Candidate ID] ),
        'Table'[Rank] < EARLIER ( 'Table'[Rank] )
    )
VAR Months =
    IF ( ISBLANK ( StartMonth ), 'Table'[Duration (Month)], 8 - StartMonth )
VAR Result =
    SWITCH (
        TRUE,
        Months < 0, 0,
        Months > 8, DIVIDE ( 'Table'[Amount] * 8, 'Table'[Duration (Month)] ),
        'Table'[Duration (Month)] > Months, DIVIDE ( 'Table'[Amount] * Months, 'Table'[Duration (Month)] ),
        DIVIDE ( 'Table'[Amount] * 'Table'[Duration (Month)], Months )
    )
RETURN
    Result

I've added a few extra lines to the example to show how it works: