Spread costs across months

[EWBWEBB]

Hi,

I have seen different iterations of this in posts but can't seem to find an option that works for me.

I'm trying to calculate the monthly spend on apprentices based on their start and end date.

The cost should be attributed as follows:

Between start and end date 80% of the apprenticship total should be paid. 

(divide 80% of total cost by number of months)

The month after apprentice ends pay the final 20%.

the number of days in the month is irrelivant i.e if there is at least one day in a month it should be added to the total number of months.

as an example:

A £100 apprenticship that lasts 10 months, ending in September should show:

£8 per month for 10 months ending in September 

£20 spend occuring in October.

The tricky bit is the length, start/end and cost can be different everytime.

I would like to be able to display this in a matrix or table and a line or column chart, ideally both but will take what I can get if there are limitations for any reason.

My data is laid out as follows:

Both relationships are inactive

Planned end date * - 1 Date

Planned start date * - 1 Date

The tables are as follows:

Period (the date range covers all relevant dates)

FactApprentice

Duration Months, 80% agreed price, 20% agreed price are calcualted columns.

Ideally I would like to be able to do this without adding another table but if required it can be.

Please cask for more detail if needed.

[tamerj1 · Accepted solution]

Hi @EWBWEBB 
Here is a sample file withe solution https://www.dropbox.com/t/uAceQzNAtArIX7y8

MONTH COST = 
SUMX (
    FactApprentice,
    VAR CurrentYearMonth = MAX ( DatePay[YEAR-MONTH] )
    VAR NumberOfMonths = DATEDIFF ( FactApprentice[Planned Start Date], FactApprentice[Planned End Date], MONTH )
    VAR StartYearMonth = YEAR ( FactApprentice[Planned Start Date] ) * 100 + MONTH ( FactApprentice[Planned Start Date] )
    VAR EndYearMonth = YEAR ( FactApprentice[Planned End Date] ) * 100 + MONTH ( FactApprentice[Planned End Date] )
    VAR MonthCost = DIVIDE ( FactApprentice[80% agreed price], NumberOfMonths )
    VAR LastMonthCost = FactApprentice[20% agreed price]
    VAR AllYearMonths = GENERATESERIES ( StartYearMonth, EndYearMonth )
    VAR FilterCondition = COUNTROWS ( INTERSECT ( VALUES ( DatePay[YEAR-MONTH] ), AllYearMonths ) )
    RETURN
        FilterCondition * IF ( CurrentYearMonth = EndYearMonth, LastMonthCost, MonthCost )
) 

[EWBWEBB]

Hey @tamerj1 

Thank you so much this was 99.9% of it but the logic is bang on!

I just tweaked slightly to account for wanting the final payment to be the month after the end date. Also noticed that DATEDIFF doesn't count it's first month so although it's and 18 month span for example there are 19 months (so just added 1).

For anyone else looking this was my final DAX.

MONTH COST =

SUMX(

FactApprentice,

VAR CurrentYearMonth = MAX( DatePay[MONTH YEAR] )

VAR NumberofMonths = DATEDIFF(FactApprentice[Planned start date],FactApprentice[Planned end date], MONTH)+1

VAR StartYearMonth = YEAR (FactApprentice[Planned start date] ) * 100 + MONTH ( FactApprentice[Planned start date] )

VAR EndYearMonth = YEAR (FactApprentice[Planned end date] ) * 100 + MONTH (EDATE( FactApprentice[Planned end date],1 ))

VAR MonthCost = DIVIDE( FactApprentice[80% agreed price], NumberofMonths )

VAR LastMonthCost = FactApprentice[20% agreed price]

VAR AllYearMonths = GENERATESERIES( StartYearMonth, EndYearMonth )

VAR FilterCondition = COUNTROWS( INTERSECT( VALUES ( DatePay[MONTH YEAR] ), AllYearMonths ) )

RETURN

FilterCondition * IF (CurrentYearMonth = EndYearMonth , LastMonthCost, MonthCost )

)

Thanks Again.

[tamerj1]

You are 100% correct

[EWBWEBB]

Hi @tamerj1 - I've just returned to try this for something else and have since removed the previous reports.

I can't remember what the DatePay[MONTH YEAR] was references, I think it was introduced for some reason.

What are the chances you can remember based on the trail in here?

[tamerj1]

Hi @EWBWEBB 
If you don't have it in the model then I would guess that it was probably added as a calculated column.
It is required in order to match both the year and the month at the same time. 

[amitchandak]

@EWBWEBB , refer if the approach in this blog can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[EWBWEBB]

Hey, thanks so much for you time, 

I've followed the step in the link but seem to be getting some odd results:

Here is my DAX for both:

Table: 

DatePay = CALENDAR(MIN(FactApprentice[Planned start date]),MAX(FactApprentice[Planned end date]))

Day By Month

MONTH COST = CALCULATE(

COUNTX(

SUMMARIZE(

FILTER(

CROSSJOIN(FactApprentice,DatePay),DatePay[Date] >= FactApprentice[Planned start date] && DatePay[Date] <= FactApprentice[Planned end date]) , FactApprentice[ULN] , DatePay[Date]) , DatePay[Date])

)

If I filter to a single individual using the FactApprentice table and the ULN I get the following

For this person it has correctly picked up the start and end months, which is the first step, (they started 01/04/2020 ended 01/10/2021)

However the cost was £6000 so should be showing £315.78......... per month for each of the months.

I can't see a reference to the value in the formula so I'm guessing i need to to do something along the lines of divide Divide month cost by itself (to get 1) then multiply that by a calculated measure of value / length months?