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

[bsas]

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?

[v-qiuyu-msft · Accepted solution]

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

Please see attached pbix file. 

Best Regards,
Qiuyun Yu

[bsas]

Thank you @v-qiuyu-msft very much. Works fine.