cancel
Showing results for
Did you mean:
Helper II

## Spread costs across months

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.

1 ACCEPTED SOLUTION
Super User

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 )
) ``````

5 REPLIES 5
Super User

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 )
) ``````

Helper II

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.

Super User

You are 100% correct

Helper II

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?

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors