Forum Discussion

EWBWEBB's avatar
EWBWEBB
Icon for Helper III rankHelper III
4 years ago
Solved

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.

Split Cost
Spreading cost
  • tamerj1's avatar
    tamerj1
    4 years ago

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

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      EWBWEBB
      Icon for Helper III rankHelper III
      4 years ago

      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.

       

    • EWBWEBB's avatar
      EWBWEBB
      Icon for Helper III rankHelper III
      4 years ago

      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?