Split period data into months and calculate fraction of yearly fee corresponding to that month

[andfre]

Hi,

I'm trying to wrap my head around a problem I'm having. My data looks a bit like below:

ID   ID2    StartDate       EndDate          YearlyFee   

1     1       2016-01-01   2016-12-31     500

1     2       2017-01-01   2017-12-31     500

2     1       2016-03-23   2017-03-22     600

2     2       2017-03-23   2018-03-22     600

I would like to view the data split into monthly data, with a earned fee-column that is a fraction of the yearly fee. Something like this:

ID   ID2    StartDate       EndDate          YearlyFee   CalendarMonth    EarnedFee 

1     1       2016-01-01   2016-12-31     500              2016-01-01          (31/366)*500

1     1       2016-01-01   2016-12-31     500              2016-02-01          (29/366)*500

1     1       2016-01-01   2016-12-31     500              2016-03-01          (31/366)*500

.............

1     2       2017-01-01   2017-12-31     500              2017-01-01          (31/365)*500

1     2       2017-01-01   2017-12-31     500              2017-02-01          (28/365)*500

.............

2     1       2016-03-23   2017-03-22     600              2016-03-01          (9/366)*600

2     1       2016-03-23   2017-03-22     600              2016-04-01          (30/366)*600

............

2     1       2016-03-23   2017-03-22     600              2017-03-01          (22/365)*600

2     2       2017-03-23   2018-03-22     600              2017-03-01          (9/365)*600

2     2       2017-03-23   2018-03-22     600              2017-04-01          (30/365)*600

............

I have tried using YEARFRAC to calculate the duration of the period as a fraction of a year, (31/365), (31/366), (28/365) etc. which seems to work fine. I have also merged my table with a date-table containing the first date of each month with the following formula

 Table = FILTER(CROSSJOIN(Table1,'Calendar'),'Calendar'[Date]>=Table1[StartdDate]&&'Calendar'[Date]<=Table1[EndDate].[Date])

But this seems to have issues with entries such as

ID   ID2    StartDate       EndDate          YearlyFee

2     1       2016-03-23   2017-03-22     600

2     2       2017-03-23   2018-03-22     600

where it only creates one entry in march 2017.

I would like to use the data to calculate a 12-month running average and such which is the reason I would like to divide the data into monthly entries.

This thread got me somewhat close and it is there I got the idea to use the formula above.
http://community.powerbi.com/t5/Desktop/Splitting-Values-between-months-of-two-dates/td-p/180075

Thank you!

[Eric_Zhang · Accepted solution]

@andfre

To achieve the expected output, you can create a calendar table and crossjoin your original table and this calendar table.

Create a calendar table:

calendar month = FILTER(CALENDAR(MIN(Table1[Start Date]),MAX(Table1[End Date])),DAY([Date])=1)

Create a  calculated table:

Table =
FILTER (
    CROSSJOIN ( Table1, 'calendar month' ),
    'calendar month'[Date]
        >= DATE ( YEAR ( Table1[Start Date] ), MONTH ( Table1[Start Date] ), 1 )
        && 'calendar month'[Date] < Table1[End Date]
)

In this new table ,you can add three new columns.

Last day column:

last day = DATE(YEAR('Table'[calendar month]),MONTH('Table'[calendar month])+1,1)-1

days column:

days =
IF (
    'Table'[last day] > 'Table'[End Date],
    DATEDIFF ( 'Table'[End Date], 'Table'[last day], DAY ) + 1,
    DATEDIFF ( 'Table'[calendar month], 'Table'[last day], DAY ) + 1
)

days2 column:

DAYS2 = DATEDIFF('Table'[Start Date],'Table'[End Date],DAY)+1

then create a measure with the DAX:

earned fee= SUM('Table'[days])/SUM('Table'[DAYS2])*SUM('table'[yearly fee])

Finally,you can show your expected result in a table visual.

See more details in the attached pbix file.

[andfre]

That works very well, thank you!