cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bsas Member
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
Moderator v-qiuyu-msft
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]))))

 

Please see attached pbix file. 

 

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
Moderator v-qiuyu-msft
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]))))

 

Please see attached pbix file. 

 

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.
Highlighted
bsas Member
Member

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

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