cancel
Showing results for
Did you mean:
Member

## add amount to next months based on duration (not cumulative result)

Hi PBI Experts,

I had to create measure, which shows distribution of revenue depending on duration of project. I did it...but after that year slicer appeared and my measure is hardcoded only for 2017. I have no idea how to modify it to be independent of manual year input.

Simple data from my table:

billing start date  new_runrate  duration UDP:

1/1/2017              20,000.00                            5

2/5/2017              34,000.00                            3

4/13/2017            45,000.00                            4

How measure shoul behave: first project started in Jan and it's duration is 5 month, another project started in Feb .

Revenue for Jan = 20K

for Feb - 20K for Feb from 1 project + 34K for Feb from second project = 54K

for March = 54K

for April 20 + 34 + 45=99

Measure for 2017:

`year_billing = CALCULATE(SUM(opportunity[new_runrate]),                 FILTER(opportunity,opportunity[billing_start_date]                   <= CALCULATE(MAX(cal[calendar_date].[Date]))),                   FILTER(opportunity,IF(opportunity[duration_UPD]=1,                    opportunity[billing_start_date],                     IF(opportunity[billing_start_date]+(opportunity[duration_UPD]*31)                       > DATE(2017,12,31),DATE(2018,1,1),                        opportunity[billing_start_date]+(opportunity[duration_UPD]*31)))                          >= CALCULATE(MIN(cal[calendar_date].[date]))))`

P.S. "calendar date" includes all dates for 3 years 2016-2018

Can someone help me with this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator

## Re: add amount to next months based on duration (not cumulative result)

Hi @bsas,

You can create a measure:

year_billing = CALCULATE(SUM(opportunity[new_runrate]),
FILTER(opportunity,opportunity[billing_start_date]
<= CALCULATE(MAX('Calendar'[Date]))),
FILTER(opportunity,IF(opportunity[duration_UPD]=1,
opportunity[billing_start_date],
IF(opportunity[billing_start_date]+(opportunity[duration_UPD]*31)
> DATE(YEAR(MAX('Calendar'[Date])),12,31),DATE(YEAR(MAX('Calendar'[Date]))+1,1,1),
opportunity[billing_start_date]+(opportunity[duration_UPD]*31)))
>= CALCULATE(MIN('Calendar'[Date]))))

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted
Moderator

## Re: add amount to next months based on duration (not cumulative result)

Hi @bsas,

You can create a measure:

year_billing = CALCULATE(SUM(opportunity[new_runrate]),
FILTER(opportunity,opportunity[billing_start_date]
<= CALCULATE(MAX('Calendar'[Date]))),
FILTER(opportunity,IF(opportunity[duration_UPD]=1,
opportunity[billing_start_date],
IF(opportunity[billing_start_date]+(opportunity[duration_UPD]*31)
> DATE(YEAR(MAX('Calendar'[Date])),12,31),DATE(YEAR(MAX('Calendar'[Date]))+1,1,1),
opportunity[billing_start_date]+(opportunity[duration_UPD]*31)))
>= CALCULATE(MIN('Calendar'[Date]))))