Prorata among range of dates

[Anonymous]

Hi, 

I am trying to calculate a prorata FTE Based on employee contract start/end date.

I have a table with the following data: ID , CONTRACT START DATE, CONTRACT END DATE, CONTRACTUAL FTE 

So I am trying to calcute the prorata based on the Start and END dates, and between those dates it return the full FTE. 

Example: 

ID	CONTRACTUAL START DATE	CONTRACTUAL END DATE	CONTRACTUAL FTE
AA	15/02/2020		100
BB	27/02/2020	14/04/2020	86

So I'd like to have as result of PRORATA FTE where i can use the total in a card visual, and have a table/matrix with ID and FTE breakdown: 

ID               JAN         FEB         MAR         APR          MAY

AA               0            51.72      100           100           100

BB               0             8.90         86          40.13            0

TOTAL         0           60.62       186        140.13            0

The formula that i di manually to achive this figures was: 

(Contract Days * FTE) / Month Days

Contract Days: Last day of the month or Contract End date - Contract start date or Fisrt day of month (inclusive)

Month Days: Last day of the month 

EMPLOYEE AA                               EMPLOYEE BB

FEB: (15 * 100)/29 = 51.72            FEB: (3 * 86)/29 = 8.90

MAR: (31*100)/31 = 100.00          MAR: (31*86)/31 = 86.00

APR: (30*100)/30 = 100.00           APR: (14*86)/30 = 40.13

MAY: (31*100)/31 = 100.00          MAY: (0*86)/31 = 0

Any help ? 

[JPCOBi]

je vois que vous aviez trouver une solution sur un calcul proraté sur les dates d un slicer.Ma demande est assez similair:

Imaginer que vous enregistriez des retards de trains 

et que vous ayez une date/heure de debut du retard et une date/heure de fin dans la database attache a powerBI

je veuw calculer le total des retards par mois mais en affectant que le retard proraté sur la fin du mois a 23h59 et sur le debut du mois a 0h01

donc il faut qu en fonction du slicer en place et des dates, powerbi me prorate pour les evenements a cheval avec just la partie retard jusque a la limite du mois (fin ou debut)

donc un retard de 6h enregistre entre 31/03/22 a 21h et 01/04/22 a 3hAM comptera pour 2 en mars et 3 en avril que je ferai la somme pour le mois de mars..

[Anonymous · Accepted solution]

HI @Anonymous,

You can refer to the following steps to achieve your requirement.

Steps:

1. Create a calendar table based on raw table date fields.

Calendar = 
CALENDAR ( MIN ( Test[StartDate] ), MAX ( MAX ( Test[EndDate] ), TODAY () ) )

2. Create a matrix visual and use raw table id as row, calendar date 'year', 'month' part as the column.

3. Write measure formulas to calculate based on the current data category label.

Measure = 
VAR currFTE =
    CALCULATE ( MAX ( Test[FTE] ), ALLSELECTED ( Test ), VALUES ( Test[ID] ) )
VAR range =
    CALCULATETABLE (
        CALENDAR (
            MIN ( Test[StartDate] ),
            IF ( MAX ( Test[EndDate] ) <> BLANK (), MAX ( Test[EndDate] ), TODAY () )
        ),
        ALLSELECTED ( Test ),
        VALUES ( Test[ID] )
    )
VAR _intersect =
    INTERSECT ( range, VALUES ( 'Calendar'[Date] ) )
VAR dayofMonth =
    DAY ( EOMONTH ( MAX ( 'Calendar'[Date] ), 0 ) )
RETURN
    IF (
        MAX ( 'Calendar'[Date] ) IN range
            || MIN ( 'Calendar'[Date] ) IN range,
        MIN ( COUNTROWS ( _intersect ), dayofMonth ) * currFTE / dayofMonth 
    )

Snapshot of results:

Regards,
Xiaoxin Sheng

[JPCOBi]

thanks for that 1st help even if i will have to deep dive as not a a great DAX programmer but..the point is that you put TODAY as date for limiting the prorata.

My point was how do you use a start and end date in a slicer  which is then giving the date to calculate the prorata.Can i name like Slicer start and Slicer End to cut the duration versus these 2 start or closure date for the period of the slicer

[Anonymous]

@Anonymous 

Hi The solution woreked perfectly, except for the Total.

As you posted in the screenshot, for FEB the total is 51.72, and should be 60.62 ( the some of the 2 values 51.72 and 8.90).

Do you know how to fix it? 

[Anonymous]

Hi @Anonymous,

You can take a look at the blow blog about measure total to use summarize function manually aggregate them to get the correct results.

Measure Totals, The Final Word  
Regards,

Xiaoxin Sheng

[Anonymous]

Thanks a lot @Anonymous

It worked perfectly. 

[amitchandak]

@Anonymous , refer if this file can help

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix?dl=0

Or this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970