Prorata the period value based on networking days

tt.xavier · March 25, 2022, 11:28am

Hi All,

Could you please assist me with calculating Prorata value based on network days and spread it between start and end date?

Problem:
I have opportunities list that has start date, end date and monthly value. How would I go about prorata the period value based on networking days for each specific period? For example, if you look at Opportunity 5 (in the attached excel file), the end date is 15/6/22. The number of networking days in June is 22 days but because the end date is 15/6, the networking days = 11. In this scenario, value for June would be ($300/22)*11 = $150. In addition, how would take public holidays into account when calculating the networking days?

Solution:
Refer to the screen shot below to see how the table would look like if the prorata calculations are applied.

I have attached both excel source file and pbix file below. Your help is appreciated. Thanks

Opportunity Analysis.xlsx (29.4 KB)
Opportunity Value Over Period - Harsh.pbix (43.8 KB)

Harsh · March 25, 2022, 11:35am

Hello @tt.xavier,

Thank You for posting your query onto the Forum.

In order to achieve the solution as per the given condition. Firstly, we’ll be required to remove any kind of active relationship between the “Dates” table and “Data” table, if exists any. We can convert them into in-active one’s. Below is the screenshot provided of the data model for the reference -

Now, create the two measures as provided below -

Total Value - Pro Rata = 
VAR _Total_Value = 
CALCULATE( SUM( Data[Value] ) , 
    TREATAS( VALUES( Dates[Date] ) , Data[First Date of Each Month] ) )

VAR _No_of_Days_In_A_Month = 
CALCULATE( COUNTROWS( Dates ) , 
    Dates[DayOfWeekName] <> "Saturday" ,
    Dates[DayOfWeekName] <> "Sunday" )

VAR _Hire_Days_In_A_Month = 
CALCULATE(
    VAR _Individual_Hire_Days = 
    FILTER( Dates , 
        Dates[Date] >= SELECTEDVALUE( Data[Start Date] ) &&
        Dates[Date] <= SELECTEDVALUE( Data[Finish Date] ) )

    VAR _Results = 
    COUNTROWS( _Individual_Hire_Days )

    RETURN
    _Results ,
        Dates[DayOfWeekName] <> "Saturday" , 
        Dates[DayOfWeekName] <> "Sunday" )

VAR _Results = 
DIVIDE( _Hire_Days_In_A_Month , _No_of_Days_In_A_Month , 0 ) * _Total_Value

RETURN
_Results

Total Value - Pro Rata - Totals = 
VAR _Cross_Joins = 
CROSSJOIN(
    VALUES( Data[Description] ) , 
    VALUES( Dates[Month & Year] ) )

VAR _Virtual_Table =
ADDCOLUMNS(
    _Cross_Joins , 
    "@Totals" , 
    [Total Value - Pro Rata] )

VAR _Remove_Blanks = 
FILTER( _Virtual_Table , 
    NOT ISBLANK( [@Totals] ) )

VAR _Results =
SUMX( _Remove_Blanks , 
    [@Totals] )

RETURN
_Results

Once these measures are created, simply put the second measure into the visuals and you’ll see the results on a pro-rata basis. Below is the screenshot of the final results provided for the reference -